INDIRECT - Referencing Another Worksheet 1

[PWD]

Good morning, I'm probably not awake enough to get this right, but I'm just trying to stop having to amend many formulae by using INDIRECT. What I'm trying to replace, for example, is:

=SUM(NOV!AC6:AI6)

by putting the values in Cells A1, A2 & A3:-

A1 = "NOV"; A2 = "AC"; A3 = "AI"

I will change these three cells according to the week and month. I've tried:

=SUM(INDIRECT(A1)&"!"&INDIRECT(A2&"6:"&A3&"6"))

but just get the "#REF" error. Each of the Indirects appears to evaluate correctly as "NOV" & "AC6:AI6" respectively.



Many thanks,
D€$

 

[combo]

You need full address string in INDIRECT's argument (to return range) and pass it to SUM:
=SUM(INDIRECT(A1&"!"&A2&"6:"&A3&"6"))

combo

 

[PWD]

Perfect!!

Many thanks,
D€$