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