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
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