tricky excel formula question

[tdols]

Hi all,

I have a question which I can easily resolve using VBA, but are unable
to resolve via a worksheet fuction.

A huge workbook with many (similar) sheets named 'Hong Kong', 'Sidney',
'Amsterdam' etc. and one 'Totals' Sheet.

On that Totals sheet:
cell B1 = Hong Kong
cell B2 = '= Hong Kong!$B$2'

so far so good.

Now can I change B1 to Sidney and have the relative value in B2 change
to '=
Sidney!$B$2'?

I've tried many things including INDIRECT and =("="& $B$1 & "!B2") etc.
but can't get it to work.

Can this be done?

Thanks in advance.

 

[Buffyslay]

i use concatenate here

set up a sort list (using data/sort/custom list)

then start the list of the sheet names

then =concatenate("='",b2,"!$B$2")

then copy, paste special - values

type 1 in c1 copy,

then.... select full range, paste special again and select 'multiply'

this should force the cells to calculate

hope this helps

 

[Buffyslay]

or re the sheet names - if you are using vba you could do a loop thingy
and print it to the immediate window to get the names quickly

from another post

' This procedure is a brief sample showing
' how to automate Excel.


' Remember to set a reference to the most current available
' Microsoft Excel object library.


' Declare object variables.
Dim appXl As Excel.Application
Dim wrkFile As Excel.Workbook
Dim wks As Object


' Set object variables.
Set appXl = New Excel.Application
' Open a file.
Set wrkFile = appXl.Workbooks.Open("c:\Dave.xls")


' Display Excel.
For Each wks In wrkFile.Sheets
Debug.Print wks.Name
Next wks


appXl.Visible = True
MsgBox "At this point Excel is open and displays a document." &
Chr$(13) &
_
"The following statements will close the document and then close
Excel."
' Close the file.
wrkFile.Close
' Quit Excel.
appXl.Quit


' Close the object references.
set wks = Nothing
Set wrkFile = Nothing
Set appXl = Nothing


HTH,
John Green - Excel MVP
Sydney
Australia

 

[Arvi Laanemets]

Hi

=INDIRECT("'" & B1 & "'!B2")


Arvi Laanemets

 

[tdols]

thanks for your replies.

I cannot use the VBA solution as our company policy doesn't allow it.

The other one seems way to complicated for an end user to perform.

Additional info: I post this request for one of our customers. The data
on the sheets is financial info which can't be changed or sorted

 

[tdols]

Works excel-lent

thanks