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. |
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. |
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. |
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? |
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. |
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? |
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. |
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. |
All times are GMT +1. The time now is 09:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com