Reference a sheet name via text in a cell.....

[lukeshuttlewood]

Hi,

As an example below is one of the calculations in my SS....

=(SUMPRODUCT(--('2004'!H1:H65000=$N7),--('2004'!C1:C65000=$L7),--('2004'!Q1:Q65000>=G7),--('2004'!Q1:Q65000<=I7),'2004'!S1:S65000))

The calculation itself is fine by the way. As you know, the bold text is reffering to a specific sheet....so heres the question:

Is it possible to make the sheet reference variable? For example change the 2004 to 2006?

I tried following:

A1 = 2004 This would be the sheet name

B1 = ' & A1 & '!A3 This would take the value of A1 as the sheet name

But it doesn't work!

Any ideas?

Thankyou
Luke

 

[GlennUK]

Look at the INDIRECT function, although I haven't tried in SUMPRODUCT in a while.

 

[lukeshuttlewood]

Nice one. this works a treat......


=INDIRECT(CONCATENATE(J15,"!D3"))

Where J15 is the sheet name!

Cool!