Indirect cell references ????

[Stephen Rainey]

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

[MattShoreson]

i think you actually want to use the INDIRECT function.

[Ardus Petus]

About "Day" problem + & sign for string concatenation

=CONCATENATE("=[parts" & FORMAT(MONTH($A21),"00")
&"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")

HTH
--
AP

"Stephen Rainey" <[email protected]> a écrit dans le message de
news: [email protected]...
> 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.
>
>
> CATENATE("=[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
>

[Ardus Petus]

About "Day" problem + & sign for string concatenation

=CONCATENATE("=[parts" & FORMAT(MONTH($A21),"00")
&"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")

HTH
--
AP

"Stephen Rainey" <[email protected]> a écrit dans le message de
news: [email protected]...
> 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.
>
>
> CATENATE("=[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
>

[Stephen Rainey]

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" <[email protected]> wrote in message
news:%[email protected]...
> About "Day" problem + & sign for string concatenation
>
> =CONCATENATE("=[parts" & FORMAT(MONTH($A21),"00")
> &"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")
>
> HTH
> --
> AP
>
> "Stephen Rainey" <[email protected]> a écrit dans le message de
> news: [email protected]...
>> 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
>>
>
>

[MattShoreson]

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")

[Ardus Petus]

=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" <[email protected]> a écrit dans le message de news:
%[email protected]...
> About "Day" problem + & sign for string concatenation
>
> "=[parts" & FORMAT(MONTH($A21),"00")
> &"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")
>
> HTH
> --
> AP
>
> "Stephen Rainey" <[email protected]> a écrit dans le message de
> news: [email protected]...
>> 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
>>
>
>

[Stephen Rainey]

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" <[email protected]> wrote in message
news:[email protected]...
> =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" <[email protected]> a écrit dans le message de news:
> %[email protected]...
>> About "Day" problem + & sign for string concatenation
>>
>> "=[parts" & FORMAT(MONTH($A21),"00")
>> &"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")
>>
>> HTH
>> --
>> AP
>>
>> "Stephen Rainey" <[email protected]> a écrit dans le message de
>> news: [email protected]...
>>> 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
>>>
>>
>>
>
>

[David Biddulph]

"Stephen Rainey" <[email protected]> wrote in message
news:[email protected]...
> "Ardus Petus" <[email protected]> wrote in message
> news:[email protected]...
>> =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

[Ardus Petus]

Ooops: typo!

Replace FORMAT() with TEXT()

HTH
--
AP

"David Biddulph" <[email protected]> a écrit dans le message de news:
[email protected]...
> "Stephen Rainey" <[email protected]> wrote in message
> news:[email protected]...
>> "Ardus Petus" <[email protected]> wrote in message
>> news:[email protected]...
>>> =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
>

[Peo Sjoblom]

"David Biddulph" <[email protected]> wrote in message
news:[email protected]...
> "Stephen Rainey" <[email protected]> wrote in message
> news:[email protected]...
>> "Ardus Petus" <[email protected]> wrote in message
>> news:[email protected]...
>>> =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