Combining Sumproduct with indirect

[pauldaddyadams]

Hi,

I have posted this previously but didnt get the required result:

so am trying again. I am sure i have seen this done but can not find it online.

I have 4 different data sheets:
Actual
Commitment
Q1 Forecast
Q2 Forecast


And I have a master sheet.

What I would like to do is have the master sheet pull data from the data sheets depending on what sheet has been selected on the Master sheet

e.g. On the Master sheet in cell G4 if this is “Actual” I would like it to return the numbers from the actual sheet.

I think I need an indirect/sumproduct formula. Can anyone help please?

I was previously helped but I do need it to include sumproduct and the ability to be able to drag the formula down and accross as my main dataset has 100+ rows

[Glenn Kennedy]

Hi there. Guess who?? is this what you'd wanted?

[pauldaddyadams]

Hi Glenn!

Is there any way in which it can be dragged down? Would I manually have to type each row?

[AlKey]

Hi Glenn!

Is there any way in which it can be dragged down? Would I manually have to type each row?

If you want to drag formula down try this one:

=INDIRECT("'"&$G$4&"'!G"&ROW($G7))

it will start at cell G7 and by pulling formula down it will change to G8, G9 and so on.

[Glenn Kennedy]

Thanks, Alkey - even I have to eat sometimes and walk away from EF...

[pauldaddyadams]

This worked! thanks Alkey for the inclusion and thanks once again to Glenn! A massive help

[AlKey]

You're welcome and thank you for the feedback!

[pauldaddyadams]

Hi,

At the moment it only looks at column G.

Can I change it so I can drag across columns as well as rows?

[Glenn Kennedy]

Will you want to go beyond column Z?

[pauldaddyadams]

Hi

I used this:

=INDIRECT("'"&K$6&"'!"&CHAR(64+COLUMN(K8))&ROW(K8))

But I will need to go past Z

[Glenn Kennedy]

Use this.

=LEFT(ADDRESS(1,COLUMNS($A:A),4),LEN(ADDRESS(1,COLUMNS($A:A),4))-1) to generate the draggable column letter. There are other (shorter) ways of doing this, but I've had them break down in some formulas. This one has worked on all occasions (so far...).

[pauldaddyadams]

Perfect, I can work with this! Cheers Glenn!

[Glenn Kennedy]

You're welcome

[AlKey]

Or you can use this smaller version

=SUBSTITUTE(ADDRESS(1,COLUMNS($A:A),4),1,"")