Dynamic Worksheet names in cells 1

[memarkiam]

Hi - I need to be able to change the name of the Worksheet, ideally by reference to the content of another cell.

EG:
=SUMIF([atvwk15.xls]storerep!$B$2:$B$201,$A$2,[atvwk15.xls]storerep!$D$2:$D$201)/SUMIF([atvwk15.xls]storerep!$B$2:$B$201,$A$2,[atvwk15.xls]storerep!$C$2:$C$201)

In the above formula, I would like the "[atvwk15.xls]storerep!" text to be replaced by a cell reference, so it would become, say:

=SUMIF(G6&$B$2:$B$201,$A$2,G6$$D$2:$D$201)/SUMIF(G6&$B$2:$B$201,$A$2,G6&$C$2:$C$201)

But I can tell you that doesn't work!
I've looked at using the ADDRESS function, but I don't think that helps me in this case, as I don't see how I can use that as a range object.

Does anyone have anu suggestions?
(I may have to generate the sheet from a Macro, where I guess I could do this quite easily, but I wanted to be able to easily update from whithin the sheet also.)

Any help greatly appreciated!

Mark,
London

 

[xlbo]

You need the indirect function:
=SUMIF(indirect(G6 & "$B$2:$B$201&quot,$A$2,G6$$D$2:$D$201)/SUMIF(indirect(G6 & "$B$2:$B$201&quot,$A$2,G6&$C$2:$C$201)


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[memarkiam]

Thanks! That's great - it hits the spot perfectly! Don't know how I missed that in the help file...

If I can be cheeky and ask a follow on question:

In the Macro I'm writing to go along with this sheet, I need to access data in several workbooks. I planned to do this by using:

Dim R1 As Range
Set R1 = Workbooks("atcwk15.xls&quot.Worksheets("atvwk15&quot.Range("B2:B201&quot

and then

LdsATV% = Application.WorksheetFunction.SumIf(R1, Store$, R2) / Application.WorksheetFunction.SumIf(R1, Store2, R3)

But this generates an error, as the Workbooks function wants an index, not a file name, but not only do I know know the index number of open files (=order in which they were opened) but I'd like to access data in files that are NOT opened, just as you can do in a cell.

Any thoughts?

Cheers,
Mark