Formula Paste with INDIRECT, text string

[lost_in_the_sauce]

Got help yesterday with INDIRECT so that a formula in cell H6 on tab1 would change which tab is looking for cell T6 based on a drop down list of the tabs - =INDIRECT($F$3&"!T6") - where $F$3 has the drop down list of tab2, tab3, tab4. Works great.

However, I need this formula to populate across a large group of cells, and when I copy/paste formula, the T6 doesn't change which I think is because it's being viewed as a text string and not a formula - but I need it as a text string for the INDIRECT to work? Is there a way to change it so that I can copy/paste the formula?

 

[Joe4]

What is the exact cell addess that the original formula is going into?
What cells (exact addresses) is it being copied to, and how exactly does that reference need to change (show us a few examples)?

One we detect the "pattern", we can probably make use of the ROW(), COLUMN(), or OFFSET() functions to do what you want.

 

[lost_in_the_sauce]

The exact cell address that the original formula is going into is cell G6 on tab "7035". I need to replicate from cell G6 down to cell G515 and across to cell W515 for a total of 8670 cells.

The exact addresses the cells are being copied to are AW6 to BM515 on tab "YoY".

The drop down list is different tabs in numerical order for areas, "7035" through "7041." The goal is to have all the cells change the tab reference when the drop down value is changed. SO, when the drop down is on "7035", all the cells are directly referencing the cells on tab "7035", and when the drop down is changed, the cells change the tab they are looking at. The tabs "7035" - "7041" are identical in format.

Does this help?

 

[Joe4]

I was referring to this part, which I thought was the main question:

I need this formula to populate across a large group of cells, and when I copy/paste formula, the T6 doesn't change

If that part of the reference in the formula is "T6" when the formula is in cell G6:
- What should the "T6" reference change to when the formula is copied to cell G7?
- What should the "T6" reference change to when the formula is copied to cell H6?

 

[lost_in_the_sauce]

Oh, sorry

=INDIRECT($F$3&"!AW6")

Cell F3 has the drop down list for the tab names

Cell G6 has this formula and when "7035" is in the dropdown, it correctly references cell AW6 on tab "7035". It's a 1:1 because the tables are identical in format, so cell G7 needs to look at cell AW7, and cell H6 needs to look at cell AX6, all the way though cell W515 looking at cell BM515 on tab "7035". No formulas, just a reference - I'm trying to make a comparison tab that can quickly reference the values from other tabs

Does that help?

 

[Joe4]

OK, if you place this formula in cell G6, it should work the way you want when you copy it across or down:

=OFFSET(INDIRECT($F$3&"!AW6"),ROW()-6,COLUMN()-7)

 

[lost_in_the_sauce]

You are a god among men. Thank you.

 

[Joe4]

You are welcome!

Just so you understand it (in case you need to use it again), here are the key components.
The OFFSET function has three main arguments:
=OFFSET(starting range, rows to offset, columns to offset)

The key here is the ROW() and COLUMN() functions.
They will return the row/column number of the cell the function is found in.
So, if you put =ROW() in cell G6, it will return 6, since you are in the 6th row.
And, if you put =COLUMN() in cell G6, it will return 7, since you are in the 7th column.

In our initial cell, we do not want to offset anything, so we want those two values to be 0.
So, if we use ROW()-6, that will get that to 0,
and if we use COLUMN()-7, that will get that one to 0.

So, as we copy across, the COLUMN() number will increase, dynamically adjusting our column reference.
and as we copy down, the ROW() number will increase, dynamically adjusting our column reference.

Hope that makes sense!