software

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 quot;summaryquot; 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(quot;=[partsquot;,MONTH($A21),quot;]widgetsquot;,DAY($A21),quot;$B$99quot;) ------------^

P.S. I am also have trouble getting the date functions to resolve to 2
digit day and month values.

Steve Rainey
About quot;Dayquot; problem amp; sign for string concatenation

=CONCATENATE(quot;=[partsquot; amp; FORMAT(MONTH($A21),quot;00quot;)
amp;quot;]widgetsquot;amp;FORMAT(DAY($A21),quot;00quot;)amp;quot;$B$99quot;)

HTH
--
AP

quot;Stephen Raineyquot; gt; a écrit dans le message de
news: ...
gt; Hi Folks,
gt; I have looked far and wide for an answer to this issue, and
gt; I am hoping that someone here can help.
gt;
gt; I create quot;summaryquot; sheets of workbooks so that important information can
gt; be seen at a glance on one page.
gt;
gt; In order to simplify the preparation of summary sheets, I want to be able
gt; to use the date value in column A
gt; to derive the name of the books and sheets referenced in adjacent cells.
gt;
gt; When attempting to concatenate text and date functions, the formula do not
gt; resolve, not sure what I am doing wrong.
gt;
gt; I hope the following illustration conveys my meaning.
gt;
gt; Any help will be appreciated.
gt;
gt; A B C
gt; D
gt; Date Orders Received Fuel Used
gt; Widgets Shipped
gt;
gt; 01 24-Dec =[book12]sheet24$A$1 =[car12]sheet24$A$26
gt; =[parts12]widgets24$B$99
gt; ............................
gt; 12 02-Aug =[book08]sheet02$A$1 =[car08]sheet02$A$26
gt; =[parts08]widgets03$B$99
gt; 13 01-Aug =[book08]sheet01$A$1 =[car08]sheet01$A$26
gt; =[parts08]widgets03$B$99
gt; 14 31-Jul =[book07]sheet31$A$1 =[car07]sheet31$A$26
gt; =[parts07]widgets31$B$99
gt; ........................
gt; 21 15-May =[book05]sheet15$A%1 =[book05]sheet15$A$26
gt; =[parts05]widgets15$B$99
gt;
gt; My attempt to put this in a formula is as follows. However it does not
gt; resolve as is desired.
gt;
gt;
gt; CATENATE(quot;=[partsquot;,MONTH($A21),quot;]widgetsquot;,DAY($A21),quot;$B$99quot;) ------------^
gt;
gt; P.S. I am also have trouble getting the date functions to resolve to 2
gt; digit day and month values.
gt;
gt; Steve Rainey
gt;

i think you actually want to use the INDIRECT function.--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: www.excelforum.com/member.php...foamp;userid=3472
View this thread: www.excelforum.com/showthread...hreadid=538023Thanks Very much quot;Ardus Petusquot; 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.

quot;Ardus Petusquot; gt; wrote in message
...
gt; About quot;Dayquot; problem amp; sign for string concatenation
gt;
gt; =CONCATENATE(quot;=[partsquot; amp; FORMAT(MONTH($A21),quot;00quot;)
gt; amp;quot;]widgetsquot;amp;FORMAT(DAY($A21),quot;00quot;)amp;quot;$B$99quot;)
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;Stephen Raineyquot; gt; a écrit dans le message de
gt; news: ...
gt;gt; Hi Folks,
gt;gt; I have looked far and wide for an answer to this issue,
gt;gt; and I am hoping that someone here can help.
gt;gt;
gt;gt; I create quot;summaryquot; sheets of workbooks so that important information can
gt;gt; be seen at a glance on one page.
gt;gt;
gt;gt; In order to simplify the preparation of summary sheets, I want to be
gt;gt; able to use the date value in column A
gt;gt; to derive the name of the books and sheets referenced in adjacent cells.
gt;gt;
gt;gt; When attempting to concatenate text and date functions, the formula do
gt;gt; not resolve, not sure what I am doing wrong.
gt;gt;
gt;gt; I hope the following illustration conveys my meaning.
gt;gt;
gt;gt; Any help will be appreciated.
gt;gt;
gt;gt; A B C D
gt;gt; Date Orders Received Fuel Used Widgets
gt;gt; Shipped
gt;gt;
gt;gt; 01 24-Dec =[book12]sheet24$A$1 =[car12]sheet24$A$26
gt;gt; =[parts12]widgets24$B$99
gt;gt; ............................
gt;gt; 12 02-Aug =[book08]sheet02$A$1 =[car08]sheet02$A$26
gt;gt; =[parts08]widgets03$B$99
gt;gt; 13 01-Aug =[book08]sheet01$A$1 =[car08]sheet01$A$26
gt;gt; =[parts08]widgets03$B$99
gt;gt; 14 31-Jul =[book07]sheet31$A$1 =[car07]sheet31$A$26
gt;gt; =[parts07]widgets31$B$99
gt;gt; ........................
gt;gt; 21 15-May =[book05]sheet15$A%1 =[book05]sheet15$A$26
gt;gt; =[parts05]widgets15$B$99
gt;gt;
gt;gt; My attempt to put this in a formula is as follows. However it does not
gt;gt; resolve as is desired.
gt;gt;
gt;gt;
gt;gt;
gt;gt; NATE(quot;=[partsquot;,MONTH($A21),quot;]widgetsquot;,DAY($A21),quot;$B$99quot;) ------------^
gt;gt;
gt;gt; P.S. I am also have trouble getting the date functions to resolve to 2
gt;gt; digit day and month values.
gt;gt;
gt;gt; Steve Rainey
gt;gt;
gt;
gt;

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(quot;A10quot;)--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: www.excelforum.com/member.php...foamp;userid=3472
View this thread: www.excelforum.com/showthread...hreadid=538023=INDIRECT(CONCATENATE(quot;=[partsquot;,MONTH($A21),quot;]widgetsquot;,DAY($A21),quot;$B$99quot;)gt;
amp;quot;]widgetsquot;amp;FORMAT(DAY($A21),quot;00quot;)amp;quot;$B$99quot;))

Not tested, but should work...

Cheers,
--
AP

quot;Ardus Petusquot; gt; a écrit dans le message de news:
...
gt; About quot;Dayquot; problem amp; sign for string concatenation
gt;
gt; quot;=[partsquot; amp; FORMAT(MONTH($A21),quot;00quot;)
gt; amp;quot;]widgetsquot;amp;FORMAT(DAY($A21),quot;00quot;)amp;quot;$B$99quot;)
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;Stephen Raineyquot; gt; a écrit dans le message de
gt; news: ...
gt;gt; Hi Folks,
gt;gt; I have looked far and wide for an answer to this issue,
gt;gt; and I am hoping that someone here can help.
gt;gt;
gt;gt; I create quot;summaryquot; sheets of workbooks so that important information can
gt;gt; be seen at a glance on one page.
gt;gt;
gt;gt; In order to simplify the preparation of summary sheets, I want to be
gt;gt; able to use the date value in column A
gt;gt; to derive the name of the books and sheets referenced in adjacent cells.
gt;gt;
gt;gt; When attempting to concatenate text and date functions, the formula do
gt;gt; not resolve, not sure what I am doing wrong.
gt;gt;
gt;gt; I hope the following illustration conveys my meaning.
gt;gt;
gt;gt; Any help will be appreciated.
gt;gt;
gt;gt; A B C D
gt;gt; Date Orders Received Fuel Used Widgets
gt;gt; Shipped
gt;gt;
gt;gt; 01 24-Dec =[book12]sheet24$A$1 =[car12]sheet24$A$26
gt;gt; =[parts12]widgets24$B$99
gt;gt; ............................
gt;gt; 12 02-Aug =[book08]sheet02$A$1 =[car08]sheet02$A$26
gt;gt; =[parts08]widgets03$B$99
gt;gt; 13 01-Aug =[book08]sheet01$A$1 =[car08]sheet01$A$26
gt;gt; =[parts08]widgets03$B$99
gt;gt; 14 31-Jul =[book07]sheet31$A$1 =[car07]sheet31$A$26
gt;gt; =[parts07]widgets31$B$99
gt;gt; ........................
gt;gt; 21 15-May =[book05]sheet15$A%1 =[book05]sheet15$A$26
gt;gt; =[parts05]widgets15$B$99
gt;gt;
gt;gt; My attempt to put this in a formula is as follows. However it does not
gt;gt; resolve as is desired.
gt;gt;
gt;gt;
gt;gt;
gt;gt; NATE(quot;=[partsquot;,MONTH($A21),quot;]widgetsquot;,DAY($A21),quot;$B$99quot;) ------------^
gt;gt;
gt;gt; P.S. I am also have trouble getting the date functions to resolve to 2
gt;gt; digit day and month values.
gt;gt;
gt;gt; Steve Rainey
gt;gt;
gt;
gt;
Hi AP, thanks for your help with this..
I have just had a breakthrough/down and have it working using the following
=INDIRECT(quot;'C:\PATH\[Daily
Log-quot;amp;TEXT(F9,quot;yyyymmddquot;)amp;quot;.xls]Activities'!$F$2quot;).
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),quot;00quot;) is put into a cell, the
following error is generated. #NAME?

Do I need to install something? What am I missing ?
quot;Ardus Petusquot; gt; wrote in message
...
gt; =INDIRECT(CONCATENATE(quot;=[partsquot;,MONTH($A21),quot;]widgetsquot;,DAY($A21),quot;$B$99quot;)gt;
gt; amp;quot;]widgetsquot;amp;FORMAT(DAY($A21),quot;00quot;)amp;quot;$B$99quot;))
gt;
gt; Not tested, but should work...
gt;
gt; Cheers,
gt; --
gt; AP
gt;
gt; quot;Ardus Petusquot; gt; a écrit dans le message de news:
gt; ...
gt;gt; About quot;Dayquot; problem amp; sign for string concatenation
gt;gt;
gt;gt; quot;=[partsquot; amp; FORMAT(MONTH($A21),quot;00quot;)
gt;gt; amp;quot;]widgetsquot;amp;FORMAT(DAY($A21),quot;00quot;)amp;quot;$B$99quot;)
gt;gt;
gt;gt; HTH
gt;gt; --
gt;gt; AP
gt;gt;
gt;gt; quot;Stephen Raineyquot; gt; a écrit dans le message de
gt;gt; news: ...
gt;gt;gt; Hi Folks,
gt;gt;gt; I have looked far and wide for an answer to this issue,
gt;gt;gt; and I am hoping that someone here can help.
gt;gt;gt;
gt;gt;gt; I create quot;summaryquot; sheets of workbooks so that important information
gt;gt;gt; can be seen at a glance on one page.
gt;gt;gt;
gt;gt;gt; In order to simplify the preparation of summary sheets, I want to be
gt;gt;gt; able to use the date value in column A
gt;gt;gt; to derive the name of the books and sheets referenced in adjacent cells.
gt;gt;gt;
gt;gt;gt; When attempting to concatenate text and date functions, the formula do
gt;gt;gt; not resolve, not sure what I am doing wrong.
gt;gt;gt;
gt;gt;gt; I hope the following illustration conveys my meaning.
gt;gt;gt;
gt;gt;gt; Any help will be appreciated.
gt;gt;gt;
gt;gt;gt; A B C D
gt;gt;gt; Date Orders Received Fuel Used Widgets
gt;gt;gt; Shipped
gt;gt;gt;
gt;gt;gt; 01 24-Dec =[book12]sheet24$A$1 =[car12]sheet24$A$26
gt;gt;gt; =[parts12]widgets24$B$99
gt;gt;gt; ............................
gt;gt;gt; 12 02-Aug =[book08]sheet02$A$1 =[car08]sheet02$A$26
gt;gt;gt; =[parts08]widgets03$B$99
gt;gt;gt; 13 01-Aug =[book08]sheet01$A$1 =[car08]sheet01$A$26
gt;gt;gt; =[parts08]widgets03$B$99
gt;gt;gt; 14 31-Jul =[book07]sheet31$A$1
gt;gt;gt; =[car07]sheet31$A$26 =[parts07]widgets31$B$99
gt;gt;gt; ........................
gt;gt;gt; 21 15-May =[book05]sheet15$A%1 =[book05]sheet15$A$26
gt;gt;gt; =[parts05]widgets15$B$99
gt;gt;gt;
gt;gt;gt; My attempt to put this in a formula is as follows. However it does not
gt;gt;gt; resolve as is desired.
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; (quot;=[partsquot;,MONTH($A21),quot;]widgetsquot;,DAY($A21),quot;$B$99quot;) ------------^
gt;gt;gt;
gt;gt;gt; P.S. I am also have trouble getting the date functions to resolve to 2
gt;gt;gt; digit day and month values.
gt;gt;gt;
gt;gt;gt; Steve Rainey
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
quot;Stephen Raineyquot; gt; wrote in message
...
gt; quot;Ardus Petusquot; gt; wrote in message
gt; ...
gt;gt; =INDIRECT(CONCATENATE(quot;=[partsquot;,MONTH($A21),quot;]widgetsquot;,DAY($A21),quot;$B$99quot;)gt;
gt;gt; amp;quot;]widgetsquot;amp;FORMAT(DAY($A21),quot;00quot;)amp;quot;$B$99quot;))
gt;gt;
gt;gt; Not tested, but should work...

gt; Hi AP, thanks for your help with this..
gt; I have just had a breakthrough/down and have it working using the
gt; following
gt; =INDIRECT(quot;'C:\PATH\[Daily
gt; Log-quot;amp;TEXT(F9,quot;yyyymmddquot;)amp;quot;.xls]Activities'!$F$2quot;).
gt; I simplified the concatenation to get the basic functionality working,
gt; and I think that I can now adopt this to my original problem.
gt;
gt; BTW: if =FORMAT(DAY(F20),quot;00quot;) is put into a cell, the
gt; following error is generated. #NAME?
gt;
gt; 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
Ooops: typo!

Replace FORMAT() with TEXT()

HTH
--
AP

quot;David Biddulphquot; gt; a écrit dans le message de news:
...
gt; quot;Stephen Raineyquot; gt; wrote in message
gt; ...
gt;gt; quot;Ardus Petusquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; =INDIRECT(CONCATENATE(quot;=[partsquot;,MONTH($A21),quot;]widgetsquot;,DAY($A21),quot;$B$99quot;)gt;
gt;gt;gt; amp;quot;]widgetsquot;amp;FORMAT(DAY($A21),quot;00quot;)amp;quot;$B$99quot;))
gt;gt;gt;
gt;gt;gt; Not tested, but should work...
gt;
gt;gt; Hi AP, thanks for your help with this..
gt;gt; I have just had a breakthrough/down and have it working using the
gt;gt; following
gt;gt; =INDIRECT(quot;'C:\PATH\[Daily
gt;gt; Log-quot;amp;TEXT(F9,quot;yyyymmddquot;)amp;quot;.xls]Activities'!$F$2quot;).
gt;gt; I simplified the concatenation to get the basic functionality working,
gt;gt; and I think that I can now adopt this to my original problem.
gt;gt;
gt;gt; BTW: if =FORMAT(DAY(F20),quot;00quot;) is put into a cell, the
gt;gt; following error is generated. #NAME?
gt;gt;
gt;gt; Do I need to install something? What am I missing ?
gt;
gt; As far as I am aware, there isn't a function FORMAT() in Excel, only in
gt; VBA. Ardus can hopefully explain what he intended?
gt; --
gt; David Biddulph
gt;

quot;David Biddulphquot; gt; wrote in message
...
gt; quot;Stephen Raineyquot; gt; wrote in message
gt; ...
gt;gt; quot;Ardus Petusquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; =INDIRECT(CONCATENATE(quot;=[partsquot;,MONTH($A21),quot;]widgetsquot;,DAY($A21),quot;$B$99quot;)gt;
gt;gt;gt; amp;quot;]widgetsquot;amp;FORMAT(DAY($A21),quot;00quot;)amp;quot;$B$99quot;))
gt;gt;gt;
gt;gt;gt; Not tested, but should work...
gt;
gt;gt; Hi AP, thanks for your help with this..
gt;gt; I have just had a breakthrough/down and have it working using the
gt;gt; following
gt;gt; =INDIRECT(quot;'C:\PATH\[Daily
gt;gt; Log-quot;amp;TEXT(F9,quot;yyyymmddquot;)amp;quot;.xls]Activities'!$F$2quot;).
gt;gt; I simplified the concatenation to get the basic functionality working,
gt;gt; and I think that I can now adopt this to my original problem.
gt;gt;
gt;gt; BTW: if =FORMAT(DAY(F20),quot;00quot;) is put into a cell, the
gt;gt; following error is generated. #NAME?
gt;gt;
gt;gt; Do I need to install something? What am I missing ?
gt;
gt; As far as I am aware, there isn't a function FORMAT() in Excel, only in
gt; VBA. Ardus can hopefully explain what he intended?
gt; --
gt; David Biddulph

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

Regards,

Peo Sjoblom

nwexcelsolutions.com