Combining references
 

OK, here is another one.

I would like to combine the sheet name and a cell value to equal a full
date. All in one cell.

I have the month and day part. That isn't a problem at all. For that, I
used a modified version of Franz's formula
Code:
--------------------
=DATEVALUE(RIGHT(CELL("filename",$A$1),LEN(CELL("f ilename",$A$1))-FIND("]",CELL("filename",$A$1))))
--------------------


This gives me whatever the sheet name is formatted as a date. However,
I would also like it to reference a cell for the year value.

Basically, I would like it reference G7 for the year portion of the
date. I have tried a few things but have not been successful in a
method. The last one I tried was to add
Code:
--------------------
&"/"&G7
--------------------
to the end of the code. Needless to say, it failed.

Thanks for all the help guys.


--
Barry Clark
------------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...o&userid=35267
View this thread: http://www.excelforum.com/showthread...hreadid=555677

Combining references
 

depends how the sheet name is formatted, but it can't be with / as that is
invalid. If it is say 26-06 or 26-Jun, then use

=DATEVALUE(RIGHT(CELL("filename",$A$1),LEN(CELL("f ilename",$A$1))-FIND("]",C
ELL("filename",$A$1)))&"-"&G7)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Barry Clark"
wrote in message
...

OK, here is another one.

I would like to combine the sheet name and a cell value to equal a full
date. All in one cell.

I have the month and day part. That isn't a problem at all. For that, I
used a modified version of Franz's formula
Code:
--------------------

=DATEVALUE(RIGHT(CELL("filename",$A$1),LEN(CELL("f ilename",$A$1))-FIND("]",C
ELL("filename",$A$1))))
--------------------


This gives me whatever the sheet name is formatted as a date. However,
I would also like it to reference a cell for the year value.

Basically, I would like it reference G7 for the year portion of the
date. I have tried a few things but have not been successful in a
method. The last one I tried was to add
Code:
--------------------
&"/"&G7
--------------------
to the end of the code. Needless to say, it failed.

Thanks for all the help guys.


--
Barry Clark
------------------------------------------------------------------------
Barry Clark's Profile:
http://www.excelforum.com/member.php...o&userid=35267
View this thread: http://www.excelforum.com/showthread...hreadid=555677

Combining references
 

Hi Barry,

if it return #value, is because you did not save the workbook, save it and
try again but, without datavalue so

=RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$ A$1))-FIND("]",CELL("filename",$A$1)))&"/"&g7

HTH
Regards from Brazil
Marcelo



"Barry Clark" escreveu:


OK, here is another one.

I would like to combine the sheet name and a cell value to equal a full
date. All in one cell.

I have the month and day part. That isn't a problem at all. For that, I
used a modified version of Franz's formula
Code:
--------------------
=DATEVALUE(RIGHT(CELL("filename",$A$1),LEN(CELL("f ilename",$A$1))-FIND("]",CELL("filename",$A$1))))
--------------------


This gives me whatever the sheet name is formatted as a date. However,
I would also like it to reference a cell for the year value.

Basically, I would like it reference G7 for the year portion of the
date. I have tried a few things but have not been successful in a
method. The last one I tried was to add
Code:
--------------------
&"/"&G7
--------------------
to the end of the code. Needless to say, it failed.

Thanks for all the help guys.


--
Barry Clark
------------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...o&userid=35267
View this thread: http://www.excelforum.com/showthread...hreadid=555677