Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky excel formula question
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...o&userid=31565 View this thread: http://www.excelforum.com/showthread...hreadid=512612 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky excel formula question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky excel formula question
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky excel formula question
Hi
=INDIRECT("'" & B1 & "'!B2") Arvi Laanemets "tdols" wrote in message ... 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...o&userid=31565 View this thread: http://www.excelforum.com/showthread...hreadid=512612 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky excel formula question
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 ------------------------------------------------------------------------ tdols's Profile: http://www.excelforum.com/member.php...o&userid=31565 View this thread: http://www.excelforum.com/showthread...hreadid=512612 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky excel formula question
Works excel-lent :) thanks -- tdols ------------------------------------------------------------------------ tdols's Profile: http://www.excelforum.com/member.php...o&userid=31565 View this thread: http://www.excelforum.com/showthread...hreadid=512612 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tricky Macro Question | Excel Worksheet Functions | |||
Tricky Excel Formula Required! | Excel Discussion (Misc queries) | |||
tricky excel formula | Excel Worksheet Functions | |||
New guy with a tricky question | Excel Programming | |||
Tricky Question | Excel Worksheet Functions |