CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Jean Cartier
Jean Cartier

Reputation: 3

How to dynamically reference rows and columns in google sheets

I've been stuck on a Google Sheets problem for a while. I have three sheets: "September", "October", and "Quantity". The September and October sheets have the same format, with a table from B2:Cxxx , where B2 and C2 are headers (name and quantity respectively). The Quantity sheet has a column B with names that match the names in column B of the September and October sheets. I want columns D and E in the Quantity sheet to be filled with values from column C of the September and October sheets based on matching the names in column B.

The formula I currently use is: =IF(ISNA(INDEX(September!C:C; MATCH('Quantity'!$B3; September!B:B; 0))); ""; INDEX(September!C:C; MATCH('Quantity'!$B3; September!B:B; 0)))

This works for September, but I want to make the sheet reference dynamic. On the Quantity sheet, the first row (row 2) of my table is named after the months (e.g., "September", "October"). Is there a way to dynamically reference the sheet name based on the value in row 2 of the same column where the formula is written?

For example, if I write the formula in column C, it should refer to "September" (from cell C2), and if I drag it to column D, it should refer to "October" (from cell D2). I need a solution that allows me to reference the sheet name dynamically, either using a formula like R1C1 style (where I reference R2 in the same column) or using the INDIRECT function. This way, I can drag the formula for future months without manually changing the sheet names.

In summary, I want to look up the value from B3 of the Quantity sheet in column B of a dynamically selected month sheet (e.g., September, October). If a match is found, it returns the corresponding value from column C of that sheet. If no match is found, it should return a blank. Basically, it’s like a VLOOKUP with error handling, but I need the sheet name to be dynamic.

I attempted to use the INDIRECT method but encountered a "parse formula error" with the following formula:

=IF(ISNA(INDEX(INDIRECT("'"&INDIRECT(ADDRESS(2, COLUMN(), 4))&"'!C:C"); MATCH(INDIRECT("B"&ROW()); INDIRECT("'"&INDIRECT(ADDRESS(2, COLUMN(), 4))&"'!B:B"); 0))); ""; INDEX(INDIRECT("'"&INDIRECT(ADDRESS(2, COLUMN(), 4))&"'!C:C"); MATCH(INDIRECT("B"&ROW()); INDIRECT("'"&INDIRECT(ADDRESS(2, COLUMN(), 4))&"'!B:B"); 0)))

To explain, if this formula is placed in D45 of the Quantity sheet, it should:

Reference the sheet name from D2 of the Quantity sheet. Look for the matching value in B45. Return the corresponding value from C45 of the dynamically selected sheet (e.g., the sheet name specified in D2). My goal was to create dynamic references based on column headers for sheet names and use row numbers to keep formulas intact, even when sorting names. Unfortunately, the formula didn’t work as expected, and I’m wondering if anyone has faced similar issues or could offer guidance on solving this.

Upvotes: 0

Views: 190

Answers (1)

Harun24hr
Harun24hr

Reputation: 36965

Use MAKEARRAY() with INDIRECT(). Makearray will scale the formula as per column and row data and you do not need drag and drop the formula.

=MAKEARRAY(ROWS(TOCOL(A3:A,1)),COLUMNS(TOROW(B2:2,1)),
LAMBDA(r,c,XLOOKUP(INDEX(A3:A,r),INDIRECT(INDEX(B2:2,c)&"!B:B"),INDIRECT(INDEX(B2:2,c)&"!C:C"))))

enter image description here

Upvotes: 2

Related Questions