User Profile
Excel_Struggler
Copper Contributor
Joined 12 months ago
User Widgets
Recent Discussions
Re: Cross sheet functionality and maintenance of the chronological order of a formula
Hi djclements, First and foremost, thank you for your detailed response. Your solution and mine both function correctly in terms of retrieval order. However, I believe the explanation could have been clearer. I tested your solutions, primarily the fill-down approach, given the inconsistent number of records in my workbook. Unfortunately, with a sufficient number of rows, I still encounter performance issues using your solution, similar to what I experienced with mine. I tested around 8,000 rows. I've attached a sample for you to test yourself. Simply adding another column with data validation for the week is not a feasible solution for achieving the desired result in my workbook. My sample is highly simplified compared to my actual workbook, so it might give that impression. Assume a high number of rows in any case (about 8.000-15.000). I'd love to hear if you have any other ideas to address my issue. I'm still inclined towards splitting the data into sub-sheets for simplicity and performance stability, but I'm unsure if this is technically feasible in Excel. Cheers936Views0likes2CommentsCross sheet functionality and maintenance of the chronological order of a formula
Hi community, I'm working on a formula that searches a range of cells and outputs it another range of cells where the criteria are based on matches on two dropdown menus which are dependent (Columns A and B) Summarized the formula does the following: 1. Lookup: Excel searches for a selected item from a corresponding dropdown menu in column B. 2. Retrieving Previous Data: When a match is identified (two identical items), Excel should search the designated columns to retrieve the previously entered data for that item. 3. Order of Retrieval: If there are multiple entries for the same item, Excel will prioritize the nearest entry relative to a reference row (This means below the reference row, it should not search upwards). For example, if the reference row is 11, and there are entries in rows 16 and 21 for the same item, Excel should retrieve the data from row 16. (You can test this by either blanking dropdown cells in columns A and B, and it will switch to the output location) =IF(B11="","",IFERROR(MAKEARRAY(5,3,LAMBDA(x,y,INDEX($G$1:$I$25,XLOOKUP(A11&B11,$A$5:$A$10&$B$5:$B$10,ROW($B$4:$B$9),,0,-1)+x,y))),"")) My problem is that I need this type of functionality across different sub-sheets so that when it starts searching in let's say week 1 (sheet 1) it will continue its search parameters in week 2 (sheet 2) and maintain the order of retrieval as mentioned in point 3 above. Essentially linking different sub-sheets together through one formula. I attached a sample with annotations to explain how the formula functions and how I need it to work. The solutions I can think of are; 1. Simplyfing the formula making it less stress intensive so that it would be possible to merge all sub-sheets into one big sheet 2. Creating a master sheet where one can perform lookups using the INDIRECT and MATCH functions I'm not sure how to fully flesh out these ideas or of other solutions however.Solved1.1KViews0likes4Comments
Groups
Recent Blog Articles
No content to show