Combining references

B

Barry Clark

OK, here is another one.

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

I have the month and day part. That isn't a problem at all. For that,
used a modified version of Franz's formula
Code
-------------------
=DATEVALUE(RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$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 th
date. I have tried a few things but have not been successful in
method. The last one I tried was to add
Code
 
B

Bob Phillips

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("filename",$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" <[email protected]>
wrote in message
news:[email protected]...
 
M

Marcelo

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:
 
Top