Dynamic INDIRECT value from another sheet

[tezza]

Hi,

I'm trying to grab data from another sheet but not getting the expect result.

The name of the sheet depends on the value in cell V1

=INDIRECT("'WK "&V1&"'!"&C2)

In this case, V1 = 29, then it should go to sheet 'WK 29'!C2 but I get a #REF error

The forumla help thinks I'm trying to read from WK 29 and the value in the current sheet at C2, (='WK 29'!45) which is wrong.

If I change it slightly to

=INDIRECT("'WK "&V1&"'!C2") then it works, however C2 become fixed, which won't drag down to D2, E2 etc.

Please help

 

[Joe4]

So, you are trying to drag this formula down and have the C2 increase to D2, E2, etc?
What cell are you putting the original formula (the one that should be returning C2) in?

 

[tezza]

So, you are trying to drag this formula down and have the C2 increase to D2, E2, etc?
What cell are you putting the original formula (the one that should be returning C2) in?

Hi Joe4

Yes, drag down and across.

The original formula will also start at C2. I'm summing 4 sheets, once I've got a working formula I'll adapt it then.

If it was just once then a simple formula will do, but it's over 12 months and I'm trying to simplify it for others to use.

 

[Joe4]

OK, if you put this formula in cell C2 and copy down, it should do what you asked for:

=OFFSET(INDIRECT("'WK " & $V$1 & "'!C2"),0,ROW()-2)

 

[tezza]

OK, if you put this formula in cell C2 and copy down, it should do what you asked for:

=OFFSET(INDIRECT("'WK " & $V$1 & "'!C2"),0,ROW()-2)

Hi,

I'm getting zero values with this one.

 

[Joe4]

OK, let's take a step back to make sure we are both working under the same assumptions.
We know that the first formula is going in cell C2, and it should be pulling from cell C2 on the other sheet.

If we drag this formula down to cell C3, what cell should that formula be pulling from on the other sheet?

And if we drag this formula across to cell D2, what cell should that formula be pulling from on the other sheet?

 

[tezza]

OK, let's take a step back to make sure we are both working under the same assumptions.
We know that the first formula is going in cell C2, and it should be pulling from cell C2 on the other sheet.

If we drag this formula down to cell C3, what cell should that formula be pulling from on the other sheet?

And if we drag this formula across to cell D2, what cell should that formula be pulling from on the other sheet?

No worries, if you drag it down then the C2 becomes C3, and across C2 becomes D2

 

[Joe4]

OK, try this formula then:

=OFFSET(INDIRECT("'WK " & $V$1 & "'!C2"),ROW()-2,COLUMN()-3)

 

[tezza]

OK, try this formula then:

=OFFSET(INDIRECT("'WK " & $V$1 & "'!C2"),ROW()-2,COLUMN()-3)

Same thing here, I notice that C2 doesn't change when dragging either way.

 

[Joe4]

Same thing here, I notice that C2 doesn't change when dragging either way.

What if you press F9?
Also, make sure that your value in cell V1 and your sheet name do not have any extra spaces or special characters in them.

It seems to work for me. Here is the sample data on my "WK 29" sheet:

And if I place the formula in cell C2 of my other sheet and drag down/across, this is what I see: