I have a Sheets workbook where I have a MASTER SHEET, and several other Sheets used to filter the Master Sheet records to various QA Worksheets. We have 7 different workflows, but I wanted to only put the same base set of data in one sheet.
We use Query() on the subsheets to control the data flow, and we have several columns on the subsheet that we enter data on as well. Each Master record only appears on 1 subsheet, and each Master Record has a computed "MasterID". The Master >> Subsheet data flow with Query works well so far.
There's one data point on the Master Sheet that needs retrieved from whichever subsheet the record is on. We know the sheet it is on, so we tried to build a VLOOKUP with darn near dynamic everything.
psuedo-ish code VLOOKUP([MasterID], [SHEET_NAME_COLUMN]!AH2: & [END_COLUMN] & 1000, [RETURN_INDEX], not sorted)
Whenever we place the real formula
=VLOOKUP(AL2, INDIRECT("'" & AH2 & "'!AH2:" & AN2 & "1000"), AO2, FALSE)
in the MasterSheet!Destination_Cell, i get blinking errors - #N/A ("we can't find the value") and #REF (You have a circular reference). Which I do - since the query function on the subsheets pulls through the MasterID in Subsheet!AH.
I've set the perms on the included link to be open, but I remember someone had a tool to create markdown tables.
I walked through several different methods of searching and returning the data i want:
- Utilize all data where it lives. In this case, The Master Sheet Columns, and an Index Page with lookup tables:
=VLOOKUP(AL2, INDIRECT(AH2 & "!A2:" & INDEX(firstFileLookups, MATCH(AH2, firstFileLookups, 0), 2) & "1000"), INDEX(firstFileLookups, MATCH(AH2, firstFileLookups, 0), 3), FALSE)
Resort to helper columns to build function strings:
=VLOOKUP(AL2, INDIRECT(AP2), AO2, FALSE)Build a new Master ID column using the same concatenation as the original, but the new Column Only lives in the Master Sheet. Still retains the circular reference error.
=VLOOKUP(AQ2, INDIRECT(AP2), AO2, FALSE)
I feel like I am either missing something really easy, or I'm taking the complete wrong approach to this. I won't be offended by either.
Your formula do work, the problem is with the query in the subsheets. You should avoid considering that column inside the query, or move that column to the right.
Maybe you can set a range with curly brackets if you need to keep it in the middle like =QUERY({'Master EDI Request List'!A:AI,'Master EDI Request List'!AL:AZ},"SELECT Col1,Col2......), or just move it to the right. By moving that column to the right it just work, as you can see: