| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indirect cell references ????
Hi Folks,
I have looked far and wide for an answer to this issue, and I am hoping that someone here can help. I create "summary" sheets of workbooks so that important information can be seen at a glance on one page. In order to simplify the preparation of summary sheets, I want to be able to use the date value in column A to derive the name of the books and sheets referenced in adjacent cells. When attempting to concatenate text and date functions, the formula do not resolve, not sure what I am doing wrong. I hope the following illustration conveys my meaning. Any help will be appreciated. A B C D Date Orders Received Fuel Used Widgets Shipped 01 24-Dec =[book12]sheet24$A$1 =[car12]sheet24$A$26 =[parts12]widgets24$B$99 ............................. 12 02-Aug =[book08]sheet02$A$1 =[car08]sheet02$A$26 =[parts08]widgets03$B$99 13 01-Aug =[book08]sheet01$A$1 =[car08]sheet01$A$26 =[parts08]widgets03$B$99 14 31-Jul =[book07]sheet31$A$1 =[car07]sheet31$A$26 =[parts07]widgets31$B$99 ......................... 21 15-May =[book05]sheet15$A%1 =[book05]sheet15$A$26 =[parts05]widgets15$B$99 My attempt to put this in a formula is as follows. However it does not resolve as is desired. =CONCATENATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99") ------------^ P.S. I am also have trouble getting the date functions to resolve to 2 digit day and month values. Steve Rainey |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect cell references ????
i think you actually want to use the INDIRECT function. -- MattShoreson ------------------------------------------------------------------------ MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472 View this thread: http://www.excelforum.com/showthread...hreadid=538023 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect cell references ????
Thanks Very much "Ardus Petus" for resolving the leading 0 issue. That is a
neat and simple solution, I like it. Do you have any idea how i can get the main formula to resolve ? I originally looked at the INDIRECT function, but I cannot see how it asists in this situation. "Ardus Petus" wrote in message ... About "Day" problem + & sign for string concatenation =CONCATENATE("=[parts" & FORMAT(MONTH($A21),"00") &"]widgets"&FORMAT(DAY($A21),"00")&"$B$99") HTH -- AP "Stephen Rainey" a écrit dans le message de news: ... Hi Folks, I have looked far and wide for an answer to this issue, and I am hoping that someone here can help. I create "summary" sheets of workbooks so that important information can be seen at a glance on one page. In order to simplify the preparation of summary sheets, I want to be able to use the date value in column A to derive the name of the books and sheets referenced in adjacent cells. When attempting to concatenate text and date functions, the formula do not resolve, not sure what I am doing wrong. I hope the following illustration conveys my meaning. Any help will be appreciated. A B C D Date Orders Received Fuel Used Widgets Shipped 01 24-Dec =[book12]sheet24$A$1 =[car12]sheet24$A$26 =[parts12]widgets24$B$99 ............................ 12 02-Aug =[book08]sheet02$A$1 =[car08]sheet02$A$26 =[parts08]widgets03$B$99 13 01-Aug =[book08]sheet01$A$1 =[car08]sheet01$A$26 =[parts08]widgets03$B$99 14 31-Jul =[book07]sheet31$A$1 =[car07]sheet31$A$26 =[parts07]widgets31$B$99 ........................ 21 15-May =[book05]sheet15$A%1 =[book05]sheet15$A$26 =[parts05]widgets15$B$99 My attempt to put this in a formula is as follows. However it does not resolve as is desired. NATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99") ------------^ P.S. I am also have trouble getting the date functions to resolve to 2 digit day and month values. Steve Rainey |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect cell references ????
the 'formula' wont resolove using concatenate as it's text. you have to use INDIRECT to resolve text to an actual formula. Right at the bottom of the help file on INDIRECT is... =INDIRECT("A10") -- MattShoreson ------------------------------------------------------------------------ MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472 View this thread: http://www.excelforum.com/showthread...hreadid=538023 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect cell references ????
=INDIRECT(CONCATENATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99")
&"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")) Not tested, but should work... Cheers, -- AP "Ardus Petus" a écrit dans le message de news: ... About "Day" problem + & sign for string concatenation "=[parts" & FORMAT(MONTH($A21),"00") &"]widgets"&FORMAT(DAY($A21),"00")&"$B$99") HTH -- AP "Stephen Rainey" a écrit dans le message de news: ... Hi Folks, I have looked far and wide for an answer to this issue, and I am hoping that someone here can help. I create "summary" sheets of workbooks so that important information can be seen at a glance on one page. In order to simplify the preparation of summary sheets, I want to be able to use the date value in column A to derive the name of the books and sheets referenced in adjacent cells. When attempting to concatenate text and date functions, the formula do not resolve, not sure what I am doing wrong. I hope the following illustration conveys my meaning. Any help will be appreciated. A B C D Date Orders Received Fuel Used Widgets Shipped 01 24-Dec =[book12]sheet24$A$1 =[car12]sheet24$A$26 =[parts12]widgets24$B$99 ............................ 12 02-Aug =[book08]sheet02$A$1 =[car08]sheet02$A$26 =[parts08]widgets03$B$99 13 01-Aug =[book08]sheet01$A$1 =[car08]sheet01$A$26 =[parts08]widgets03$B$99 14 31-Jul =[book07]sheet31$A$1 =[car07]sheet31$A$26 =[parts07]widgets31$B$99 ........................ 21 15-May =[book05]sheet15$A%1 =[book05]sheet15$A$26 =[parts05]widgets15$B$99 My attempt to put this in a formula is as follows. However it does not resolve as is desired. NATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99") ------------^ P.S. I am also have trouble getting the date functions to resolve to 2 digit day and month values. Steve Rainey |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect cell references ????
Hi AP, thanks for your help with this..
I have just had a breakthrough/down and have it working using the following =INDIRECT("'C:\PATH\[Daily Log-"&TEXT(F9,"yyyymmdd")&".xls]Activities'!$F$2"). I simplified the concatenation to get the basic functionality working, and I think that I can now adopt this to my original problem. BTW: if =FORMAT(DAY(F20),"00") is put into a cell, the following error is generated. #NAME? Do I need to install something? What am I missing ? "Ardus Petus" wrote in message ... =INDIRECT(CONCATENATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99") &"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")) Not tested, but should work... Cheers, -- AP "Ardus Petus" a écrit dans le message de news: ... About "Day" problem + & sign for string concatenation "=[parts" & FORMAT(MONTH($A21),"00") &"]widgets"&FORMAT(DAY($A21),"00")&"$B$99") HTH -- AP "Stephen Rainey" a écrit dans le message de news: ... Hi Folks, I have looked far and wide for an answer to this issue, and I am hoping that someone here can help. I create "summary" sheets of workbooks so that important information can be seen at a glance on one page. In order to simplify the preparation of summary sheets, I want to be able to use the date value in column A to derive the name of the books and sheets referenced in adjacent cells. When attempting to concatenate text and date functions, the formula do not resolve, not sure what I am doing wrong. I hope the following illustration conveys my meaning. Any help will be appreciated. A B C D Date Orders Received Fuel Used Widgets Shipped 01 24-Dec =[book12]sheet24$A$1 =[car12]sheet24$A$26 =[parts12]widgets24$B$99 ............................ 12 02-Aug =[book08]sheet02$A$1 =[car08]sheet02$A$26 =[parts08]widgets03$B$99 13 01-Aug =[book08]sheet01$A$1 =[car08]sheet01$A$26 =[parts08]widgets03$B$99 14 31-Jul =[book07]sheet31$A$1 =[car07]sheet31$A$26 =[parts07]widgets31$B$99 ........................ 21 15-May =[book05]sheet15$A%1 =[book05]sheet15$A$26 =[parts05]widgets15$B$99 My attempt to put this in a formula is as follows. However it does not resolve as is desired. ("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99") ------------^ P.S. I am also have trouble getting the date functions to resolve to 2 digit day and month values. Steve Rainey |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect cell references ????
"Stephen Rainey" wrote in message
... "Ardus Petus" wrote in message ... =INDIRECT(CONCATENATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99") &"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")) Not tested, but should work... Hi AP, thanks for your help with this.. I have just had a breakthrough/down and have it working using the following =INDIRECT("'C:\PATH\[Daily Log-"&TEXT(F9,"yyyymmdd")&".xls]Activities'!$F$2"). I simplified the concatenation to get the basic functionality working, and I think that I can now adopt this to my original problem. BTW: if =FORMAT(DAY(F20),"00") is put into a cell, the following error is generated. #NAME? Do I need to install something? What am I missing ? As far as I am aware, there isn't a function FORMAT() in Excel, only in VBA. Ardus can hopefully explain what he intended? -- David Biddulph |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect cell references ????
Ooops: typo!
Replace FORMAT() with TEXT() HTH -- AP "David Biddulph" a écrit dans le message de news: ... "Stephen Rainey" wrote in message ... "Ardus Petus" wrote in message ... =INDIRECT(CONCATENATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99") &"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")) Not tested, but should work... Hi AP, thanks for your help with this.. I have just had a breakthrough/down and have it working using the following =INDIRECT("'C:\PATH\[Daily Log-"&TEXT(F9,"yyyymmdd")&".xls]Activities'!$F$2"). I simplified the concatenation to get the basic functionality working, and I think that I can now adopt this to my original problem. BTW: if =FORMAT(DAY(F20),"00") is put into a cell, the following error is generated. #NAME? Do I need to install something? What am I missing ? As far as I am aware, there isn't a function FORMAT() in Excel, only in VBA. Ardus can hopefully explain what he intended? -- David Biddulph |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect cell references ????
"David Biddulph" wrote in message ... "Stephen Rainey" wrote in message ... "Ardus Petus" wrote in message ... =INDIRECT(CONCATENATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99") &"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")) Not tested, but should work... Hi AP, thanks for your help with this.. I have just had a breakthrough/down and have it working using the following =INDIRECT("'C:\PATH\[Daily Log-"&TEXT(F9,"yyyymmdd")&".xls]Activities'!$F$2"). I simplified the concatenation to get the basic functionality working, and I think that I can now adopt this to my original problem. BTW: if =FORMAT(DAY(F20),"00") is put into a cell, the following error is generated. #NAME? Do I need to install something? What am I missing ? As far as I am aware, there isn't a function FORMAT() in Excel, only in VBA. Ardus can hopefully explain what he intended? -- David Biddulph Maybe Ardus is not English/American, it certainly looks like the equivalent of the TEXT function to me -- Regards, Peo Sjoblom http://nwexcelsolutions.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing cell references in formulas to names and back again. | Excel Discussion (Misc queries) | |||
| Automatically Changing Cell Reference's when Pasting in Excel | Excel Discussion (Misc queries) | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
| Cell references change when entering new data | New Users to Excel | |||
| Changing cell references automatically | New Users to Excel | |||
Linear Mode
