User Profile
Khizar_Hayat
Brass Contributor
Joined Apr 27, 2020
User Widgets
Recent Discussions
Re: Timeline Not Matching Other Filters
While not a direct solution, using slicers for the "Six Month Review" filter might provide better visual control over the data. OR Create a Calculated Field: If your data model supports it, create a calculated field that combines the "Six Month Review" logic with the date. This calculated field can then be used for the Timeline filter. Create a Measure: In Power Pivot or Data Model, create a measure that calculates the "Six Month Review" items and use this in the PivotTable OR Create dynamic named ranges for "Six Month Review" items and all items. Use these named ranges as data sources for PivotTables. Update the named ranges based on other filter criteriaRe: Conditional Formatting with Multiple Cell Values
Select the Range: Select the range of cells you want to format (e.g., E2:P2). This is the range that will change color based on the conditions. Create a New Rule: Go to the Home tab, click Conditional Formatting, and then "New Rule". Use a Formula to Determine Which Cells to Format: Choose this option. Enter the Formula: This is where the logic for your conditions will go. For the red condition: =AND(ISNUMBER(E2),H2="F1IL",ISBLANK(J2),ISBLANK(P2)) For the green condition: =AND(ISNUMBER(E2),H2<>"F1IL",ISBLANK(J2),ISNUMBER(P2)) Format the Cells: Click the "Format" button to choose the red fill color for the first condition and green for the second. Create Another Rule: Repeat steps 2-5 for the green condition. Order Matters: Ensure the red condition is checked first in the Conditional Formatting Rules Manager (Home -> Conditional Formatting -> Manage Rules). This is important because if both conditions are met, the first one will apply.Re: pull data from list
Problem: You want to extract two specific groups of data from a larger dataset: locations with "Empty" items and locations with "No order for today" in the "Quantity" column. Solution: We'll use the FILTER function to extract these two groups separately and then combine them into a single table. Step-by-Step Guide 1. Prepare Your Data: Ensure your data is in a table format. This makes it easier to work with and reference ranges. You can convert your range to a table by selecting the data and pressing Ctrl+T. Name your table for easier reference. Let's call it "Data". 2. Create Separate Filtered Lists: Create two new sheets or areas in your existing sheet to hold the filtered data. In the first area, use the FILTER function to extract locations with "Empty" items: Excel =FILTER(Data,Data[Items]="Empty") Use code with caution. In the second area, use the FILTER function to extract locations with "No order for today" in the "Quantity" column: Excel =FILTER(Data,Data[Quantity]="No order for today") Use code with caution. 3. Combine the Filtered Lists: There are a few ways to combine these lists: Concatenate with Rows: Use the CONCAT function to combine the two filtered ranges vertically. Create a Pivot Table: Create a Pivot Table based on the two filtered ranges to summarize the data. Manual Combination: If you prefer manual control, you can copy and paste the results from the two filtered lists into a new sheet and arrange them as needed. Example Formulas Assuming your data is in a table named "Data" with columns "Location", "Items", and "Quantity": Sheet 1: Cell A1: =FILTER(Data,Data[Items]="Empty") Cell A10: =FILTER(Data,Data[Quantity]="No order for today") Sheet 2: (Using CONCAT to combine) Cell A1: =CONCAT(Sheet1!A1:C10,Sheet1!A11:C20) This will create a single list with all "Empty" and "No order for today" locations. Additional Tips Error Handling: Use IFERROR to handle cases where no matches are found in the FILTER function. Sorting: Sort the combined list by location or other criteria as needed. Formatting: Apply formatting to the combined list to improve readability. By following these steps, you should be able to effectively extract and combine the desired data from your Excel sheet.Re: Merging multiple lines if one range selcted
Find download link below https://2excel.s3.amazonaws.com/planning/reservation/Booking_Reservation_Calendar.zip?X-Amz-Content-Sha256=UNSIGNED-PAYLOAD&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAJWSDQAZWJSDCC4GA%2F20240817%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20240817T061848Z&X-Amz-SignedHeaders=host&X-Amz-Expires=1200&X-Amz-Signature=03d7d3f6a6a3210b8fab2ef392d474b71fede5958d30829115265fa3a1620bbeRe: Excel- auto formula
Unfortunately, Excel cannot directly apply formulas to images. Images are treated as static objects within the spreadsheet, and Excel doesn't have the capability to extract numerical data from images and perform calculations on them. If you can provide more details about the image format, the type of data it contains, and the frequency of this task, I might be able to offer more specific recommendations.Re: best way to find new part numbers from one spreadsheet to another
the best way is to use conditional formatting to highlight duplicates it is done by using the data from 1st spread sheet and select conditional formatting go to new rule, select a new formula for cell than type formula: countif(range of 2 worksheet, select first cell of 2 worksheet)>0 than fill the box with the colour you want to see it then click ok duplicated will be highllighted.... nonchriste for better understanding sharing a sample file my friend
