Dynamic Reference to Worksheet in Excel v.X

[Will Deatrick]

Is it possible to dynamically select a worksheet reference in a
formula? For example, I have a series of worksheets for each player of
a game, Player1, Player2, and so on. Each player's sheet has a useful
statisic in cell E14 that I would like to summarize on another sheet.
On that summary sheet, the reference to each player's sheet is static.

1 =Player1!E14+45
2 =Player2!E14+45

Is it possible to replace the static "Player1", "Player2" reference
with a variable reference to a cell somewhere else on the page.
Something like:

1 = 'A4'!E14+45
2 = 'A5'!E14+45
3
4 "Player1"
5 "Player2"

My real goal is reduce the amount of editing I have to do in order to
change the summary statistics. If I want to change the "+45" to "+50"
for each player, I would like to be able to change only the first cell
and then "Fill->Down", but I can't do so when the worksheet references
are static. Is there some other way around this issue?

Thanks, Will

 

[Bernard REY]

Use the "INDIRECT" function. A formula like:
"=INDIRECT(B1&"!E14") + 45" (without the quotes)
could be the answer, provided in B1 you have the "Player1" value, as
indicated. INDIRECT evaluates the text string as a formula and is sometimes
very helpful.

But there might be another way to modify your fomula (depending unpon what
you really want to do) : change your original formulas to:
1 "=Player1!E14 + $B$1"
2 "=Player2!E14 + $B$1"
Where the value in B1 is "45". So you'll only have to change the value in B1
to change the lot.

---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

--
Bernard Rey - Toulouse / France
MVP - Macintosh


Will Deatrick wrote :

 

[Will Deatrick]

Hi Bernard:

I tried your suggestion, but Excel gave me an error message stating I
had an error in my formula. Were you able to get this to work?

Thanks,

Will

 

[Bernard REY]

Will Deatrick wrote :

I tried your suggestion, but Excel gave me an error message stating I
had an error in my formula. Were you able to get this to work?

Yes, of course (I usually test it through before giving a hint)

Can you post the *exact* formula you typed in when there there was that
error message? There must be something wrong in it, but it has to be found
out.

You could also try step by step:
=INDIRECT("Player1!E14")
(this should give exactly the same result as: =Player!E14)

Then (let's say the content of cell B1 is "Player1") try:
=INDIRECT(B1&"!E14")

Then:
=INDIRECT(B1&"!E14") + 45

Does this run as expected?
If not at what point are things going wrong?

 

[Neil Carvin]

Hi Neil:

Thank you for your suggestion. I've never used VBA, but I will give
your approach a try and see if I can get it to work. Will this approach
also work with the A1 notation?

Thanks, Will

Yes, there's no dependence on the notation style. If you've never used VBA,
you will have to open the Visual Basic Editor from the Tools menu, select
Module from the Insert menu, and paste in the function. Quit or close the
VB Editor, and you can then use the function in a manner more or less
similar to Excel's built-in functions.

Neil

Just for fun, I did this with a little vba function.

Function GetPersonsScore(myName As String, myConstant As Double)

On Error GoTo myError

GetPersonsScore = _
ThisWorkbook.Sheets(myName).Cells(1, 1).Value + myConstant

Exit Function

myError:
GetPersonsScore = 0

End Function


Assume you have a main worksheet and three others named Tom, Dick, and
Harry. Further assume that the value you need for each person is stored in
the first row, first column of his unique worksheet.

On the main worksheet:

1 2
1 Sometext 45

2 Tom =GetPersonsScore(rc[-1], r1c2)
3 Dick =GetPersonsScore(rc[-1], r1c2)
4 Harry =GetPersonsScore(rc[-1], r1c2)


Please forgive the r1c1 notation. It's what I always use.

Use the "INDIRECT" function. A formula like:
"=INDIRECT(B1&"!E14") + 45" (without the quotes)
could be the answer, provided in B1 you have the "Player1" value, as
indicated. INDIRECT evaluates the text string as a formula and is sometimes
very helpful.

But there might be another way to modify your fomula (depending unpon what
you really want to do) : change your original formulas to:
1 "=Player1!E14 + $B$1"
2 "=Player2!E14 + $B$1"
Where the value in B1 is "45". So you'll only have to change the value in B1
to change the lot.

 

[Will Deatrick]

Hi Bernard:

Many apologies - I must have been too tired to follow instructions
properly last night. I tried again this morning, and INDIRECT worked
exactly as you stated. I am able to change one field in my list, and
Excel does the Fill->Down (i.e. calculated column) for me and all
fields reference the correct player's statistics.

Excellent work Bernard! Thank you for being patient with me.

Best Regards,

Will