Tags: easily, excel, formula, fuction, huge, microsoft, msdn, resolve, software, tricky, unable, vba, via, workbook, worksheet

tricky excel formula question

On Microsoft » Microsoft Excel

4,946 words with 5 Comments; publish: Fri, 23 May 2008 03:37:00 GMT; (30692.77, « »)

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.

--

tdols

---

tdols's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31565

View this thread: http://www.excelforum.com/showthread.php?threadid=512612

All Comments

Leave a comment...

  • 5 Comments
    • 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

      #1; Fri, 23 May 2008 03:38:00 GMT
    • 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

      #2; Fri, 23 May 2008 03:39:00 GMT
    • Hi

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

      Arvi Laanemets

      "tdols" <tdols.239mv0_1139998503.3.excel.todaysummary.com.excelforum-nospam.com> wrote in message

      news:tdols.239mv0_1139998503.3.excel.todaysummary.com.excelforum-nospam.com...

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

      >

      > --

      > tdols

      > ---

      > tdols's Profile:

      http://www.excelforum.com/member.php?action=getinfo&userid=31565

      > View this thread: http://www.excelforum.com/showthread.php?threadid=512612

      >

      #3; Fri, 23 May 2008 03:40:00 GMT