Forum Discussion

vevans8081's avatar
vevans8081
Copper Contributor
Sep 03, 2024

Formula that changes the sheet reference AND the cell being referenced

I have a file that has a summary page, and pulls that summary page from Sheet A, B or C based on a dropdown. Sheets A, B & C are identical to each other.

On the summary page there is a box with a dropdown to tell excel which sheet to pull the data from - this is cell B5. Currently, the formulas are set up as... if($B$5="Sheet A",SheetA!U18,if($B$5="Sheet B",SheetB!U18,if($B$5="Sheet C",SheetC!U18)))

What I would like to see is something that allows me to point to cell U18 on whichever sheet is referenced in B5. I know that I can do that using an INDIRECT formula (INDIRECT($B$5&"!U18"), but this is where is gets tricky for me.

I'd like the INDIRECT formula to change the cell (U18) as I drag the formula over - so it would be U18, then V18, then W18 and so on. And since the sheet has multiple rows that this formula will be in, with different cells (think U19, U20, U21...) AND there is always the possibility that the referenced sheets have rows added or subtracted, I don't want to hard code the cell reference. Meaning, I don't want the formula to point to B5 for the sheet number, then W65 (for example) for the U18, X65 for the V18, etc to drop into the INDIRECT formula.

The IF formulas are working for me just fine, but I'd love to be able to drive this all by the dropdown and not have to write out the long if statement. Future spreadsheets may not be so simple.

excel
  • adomsmith's avatar
    adomsmith
    Copper Contributor
    Sep 04, 2024

    To achieve this, you can use the INDIRECT function in combination with ADDRESS or INDEX to dynamically reference the correct cell based on the dropdown selection and the relative position of the formula as you drag it across rows and columns.

    Here's how you can do it:

    Step-by-Step Solution:

    1. Use the INDIRECT function to dynamically reference the sheet based on the value in cell B5.
    2. Use ADDRESS to dynamically create the cell reference as you drag the formula across columns and rows.
    3. Combine the sheet name and the cell address using INDIRECT.

    Formula:

    If your dropdown is in B5 and the cell reference you want to start with is U18, use this formula:

     

    =INDIRECT("'" & $B$5 & "'!" & ADDRESS(ROW($U$18), COLUMN(U18)))

     

    Explanation:

    1. $B$5: This is the cell containing the sheet name (e.g., "Sheet A").
    2. ROW($U$18): This returns the row number of the reference cell (18 in this case). The $ before U18 makes the row reference absolute, so it doesn’t change as you drag the formula across columns.
    3. COLUMN(U18): This returns the column number of the reference cell (U in this case). The column reference is relative (no $ before U), so it changes as you drag the formula across columns.
    4. ADDRESS(ROW($U$18), COLUMN(U18)): This creates the cell reference as a text string (e.g., "U18", "V18", etc.) based on the current row and column.
    5. INDIRECT("'" & $B$5 & "'!" & ADDRESS(...)): Combines the sheet name from B5 with the dynamically generated cell reference to fetch the value from the correct cell in the correct sheet.

    Example:

    • If B5 is "Sheet A" and the formula is in the cell that corresponds to U18 in "Sheet A", it will return the value from SheetA!U18.
    • If you drag the formula to the right, it will return values from SheetA!V18, SheetA!W18, etc.
    • If you drag it down, it will adjust for the row, returning values like SheetA!U19, SheetA!U20, etc.

    This approach allows you to dynamically reference both the sheet and the specific cell without hard-coding the cell references, making your summary sheet much more flexible and easier to manage as your workbook changes.

Resources