Indirect with Sumproduct

[pauldaddyadams]

Hi,

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?

[Special-K]

in 'P&L Master'!G7
=INDIRECT("'"&G4&"'!G7")
in 'P&L Master'!G8
=INDIRECT("'"&G4&"'!G8")

SUMPRODUCT not needed

[NeedForExcel]

Here!

Put this in G7 - =INDIRECT($G$4&"!G7")
& in G8 - =INDIRECT($G$4&"!G8")

Just do 1 thing, Remove all spaced from Sheet Names and Drop Down List

[NeedForExcel]

@ Special-K - This is great "'"

[pauldaddyadams]

Hi

Thanks for posting! I can't get on the site at work, having to use my phone.

Can the formula be adapted so I can drag the cells down and across rather than change the reference each time?