Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect worksheet function
The following is a hardcoded formula in a worksheet, call it the
CurrentSheet.. ='210208345'!FSPTX/'210208345'!CurrentValue Where '210208345' is the name of a worksheet, and FSPTX and CurrentValue are names defined in thgat worksheet. I can get the same reult using =INDIRECT("'210208345'!FSPTX")/INDIRECT("'210208345'!CurrentValue") But I'd rather not have to hardcode the 210208345, and FSPTX, as they are values in cells in the CurrentSheet. Is the following "best'? =INDIRECT("'"&D$2&"'!"&B3)/INDIRECT("'"&D$2&"'!CurrentValue") So B3 would change to B4 in the cell below. -- http://www.standards.com/; See Howard Kaikow's web site. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect worksheet function
Well it is dynamic, and it does what you want, so I guess it is 'best'. You
could use names to hold the values, but is that better, probably not. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Howard Kaikow" wrote in message ... The following is a hardcoded formula in a worksheet, call it the CurrentSheet.. ='210208345'!FSPTX/'210208345'!CurrentValue Where '210208345' is the name of a worksheet, and FSPTX and CurrentValue are names defined in thgat worksheet. I can get the same reult using =INDIRECT("'210208345'!FSPTX")/INDIRECT("'210208345'!CurrentValue") But I'd rather not have to hardcode the 210208345, and FSPTX, as they are values in cells in the CurrentSheet. Is the following "best'? =INDIRECT("'"&D$2&"'!"&B3)/INDIRECT("'"&D$2&"'!CurrentValue") So B3 would change to B4 in the cell below. -- http://www.standards.com/; See Howard Kaikow's web site. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect worksheet function
Try...
=INDIRECT("'"&D$2&"'!"&CELL("address",B3))/INDIRECT("'"&D$2&"'!CurrentVal ue") Hope this helps! In article , "Howard Kaikow" wrote: The following is a hardcoded formula in a worksheet, call it the CurrentSheet.. ='210208345'!FSPTX/'210208345'!CurrentValue Where '210208345' is the name of a worksheet, and FSPTX and CurrentValue are names defined in thgat worksheet. I can get the same reult using =INDIRECT("'210208345'!FSPTX")/INDIRECT("'210208345'!CurrentValue") But I'd rather not have to hardcode the 210208345, and FSPTX, as they are values in cells in the CurrentSheet. Is the following "best'? =INDIRECT("'"&D$2&"'!"&B3)/INDIRECT("'"&D$2&"'!CurrentValue") So B3 would change to B4 in the cell below. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect worksheet function
"Domenic" wrote in message
... Try... =INDIRECT("'"&D$2&"'!"&CELL("address",B3))/INDIRECT("'"&D$2&"'!CurrentVal ue") How does using CELL("address",B3) differ from using just B3? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect worksheet function
In article ,
"Howard Kaikow" wrote: "Domenic" wrote in message ... Try... =INDIRECT("'"&D$2&"'!"&CELL("address",B3))/INDIRECT("'"&D$2&"'!CurrentVal ue") How does using CELL("address",B3) differ from using just B3? For one thing, it could change the cell reference if your inserted or deleted a row or column in the sheet which contains the formula rather than the source sheet. That would be either a good or a bad idea, depending on what you're trying to achieve. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect worksheet function
Let's say that D2 holds sheet_name, and cell B3 contains A1.
The formula =INDIRECT("'"&D$2&"'!"&CELL("address",B3)) returns A1, that is the contents of B3. The formula =INDIRECT("'"&D$2&"'!"&B3) retuns whatever is in A1, that is the cell pointed to by B3. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Howard Kaikow" wrote in message ... "Domenic" wrote in message ... Try... =INDIRECT("'"&D$2&"'!"&CELL("address",B3))/INDIRECT("'"&D$2&"'!CurrentVal ue") How does using CELL("address",B3) differ from using just B3? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect worksheet function
In article ,
"Howard Kaikow" wrote: "Domenic" wrote in message ... Try... =INDIRECT("'"&D$2&"'!"&CELL("address",B3))/INDIRECT("'"&D$2&"'!CurrentVal ue") How does using CELL("address",B3) differ from using just B3? Sorry, I misunderstood. Disregard my post. CELL("Address",B3) returns the address for the B3 in the form of a text value -- $B$3. INDIRECT then uses it, together with the text string in D2, to return a reference. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect worksheet function
"Domenic" wrote in message
... Sorry, I misunderstood. Disregard my post. CELL("Address",B3) returns the address for the B3 in the form of a text value -- $B$3. INDIRECT then uses it, together with the text string in D2, to return a reference. Lemme clarify. THe values in cells C2, D2, etc. are the hard coded names of worksheets, each representing a different investment portfolio. The values in B3, B4, etc. are the hard coded stock symbols. The formula calculates the % in each portfolio for each stock. So =INDIRECT("'"&C$2&"'!"&B5)/INDIRECT("'"&C$2&"'!CurrentValue") returns the percent of the portfolio on sheet with name in C2 for the stock with name in B5. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Return array from worksheet function | Excel Worksheet Functions | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
INDIRECT Function impact on Copy Worksheet | Excel Worksheet Functions |