excel - referencing worksheets according to name/text string in another cell

[mankymummymoo]

hi, i am trying to reference cell references from one worksheet to another based on the value (which is the worksheet name) of another cell...

so i have four worksheets - summary, shop1, shop2 and shop3.

in summary i want to basically reference cells in each of the other worksheets.

so in summary i want to reference A1, A2, A3, B1, B2, B3 etc. in shop1, and then further down the page those same cells but in shop2 and shop3

so my cell reference would look something like this - =shop1!a1.

thats fine but i want to be able to copy down this formula to a range of cells.

and then i want to reference the same cells again in a number of cells but referencing shop2.

is there a way of doing this dynamically so that i can refer to a cell that has the worksheet name in (ie. replacing the shop1 or shop2 with a cell reference that has a value in it (something like =(cell $Z$1's value!a1). and then be able to copy this down and across so that down it would read =(cell $Z$1's value!a2), =(cell $Z$1's value!a3) etc.

good lord, if that makes any sense to anyone i would be extremely grateful - the only other solution i can see is manually changing the worksheet name on each and every cell, and there are hundreds!

Ooo. I hope someone answers you in something approximating English, because I think you're asking about something that would be useful to me, too. (I'm not quite sure I've understood your whole post, but I have to use Excel sometimes, even though I don't really want to)

Yes it is definitely possible to do this and I know I've done it, but I'm not at work so can't find the answer right now, but I honestly think you'd be better off googling the relevant words, Mr Excel and Ozgrid usually have a bunch of brilliant examples.

I think the function you're looking for is INDIRECT, but can't swear to it, I always just throw a few relevant words at Google.

hi, thanks for your responses... i have tried googling (and bing-ing!) and asked a similar question a while back on mn and got wonderful response using INDIRECT but i think what i'm asking for is something simpler than last time.

Alternatively, if there's only 4 sheets and you don't want a future-proof solution, just select the ones you want to change and FIND "shop 1" and REPLACE with "shop 2" it will replace the text within the formulae.
Not elegant, but bloody quick.

I think you want index/match for this. Have a look here and let us know if it's useful :)

oooh am liking ABL's idea - guess i could just select the range of cells i want to change the worksheet name in?

will look at WW too once DS in bed.

thanks sooooo much.

Hi,
INDIRECT is certainly one way to do this. So let's say:

• In cell Z1 you've got your shop name (Shop1).
• You want to get the value of cell A1 on the Shop1 sheet into any cell on the Summary sheet.

To do this, enter this formula into a cell on the Summary sheet:

=INDIRECT("'" & $Z$1 &"'!" & ADDRESS(ROW(A1), COLUMN(A1)))

You can then copy this formula down/across to get the values from A2, A3 etc.

When you want to get the values from Shop2, simply replace the $Z$1 part in the above formula to refer to the cell that contains the word Shop2.

Hope that helps!

www.wiseowl.co.uk/blog/excel/index.htm