Indirect cell references ????

S

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
 
A

Ardus Petus

About "Day" problem + & sign for string concatenation

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

HTH
 
S

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 said:
About "Day" problem + & sign for string concatenation

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

HTH
--
AP

Stephen Rainey said:
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
Click to expand...
Click to expand...
 
M

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

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 said:
About "Day" problem + & sign for string concatenation

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

HTH
--
AP

Stephen Rainey said:
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
Click to expand...
Click to expand...
 
S

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 said:
=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 said:
About "Day" problem + & sign for string concatenation

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

HTH
--
AP

Stephen Rainey said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

David Biddulph

Stephen Rainey said:
Ardus Petus said:
=INDIRECT(CONCATENATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99")>
&"]widgets"&FORMAT(DAY($A21),"00")&"$B$99"))

Not tested, but should work...
Click to expand...
Click to expand...
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 ?
Click to expand...

As far as I am aware, there isn't a function FORMAT() in Excel, only in VBA.
Ardus can hopefully explain what he intended?
 
A

Ardus Petus

Ooops: typo!

Replace FORMAT() with TEXT()

HTH
--
AP

David Biddulph said:
Stephen Rainey said:
Ardus Petus said:
=INDIRECT(CONCATENATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99")>
&"]widgets"&FORMAT(DAY($A21),"00")&"$B$99"))

Not tested, but should work...
Click to expand...
Click to expand...
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 ?
Click to expand...

As far as I am aware, there isn't a function FORMAT() in Excel, only in
VBA. Ardus can hopefully explain what he intended?
Click to expand...
 
P

Peo Sjoblom

David Biddulph said:
Stephen Rainey said:
Ardus Petus said:
=INDIRECT(CONCATENATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99")>
&"]widgets"&FORMAT(DAY($A21),"00")&"$B$99"))

Not tested, but should work...
Click to expand...
Click to expand...
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 ?
Click to expand...

As far as I am aware, there isn't a function FORMAT() in Excel, only in
VBA. Ardus can hopefully explain what he intended?
Click to expand...

Maybe Ardus is not English/American, it certainly looks like the equivalent
of the TEXT function to me


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
Top