Macro or Formula to have a cell match the date on the tab.

[Cindy]

I have a macro that assigns the Day and Date of the month to each tab (Thanks
to the folks on this board) but I would like it fill the corresponding day
and date in a cell on each worksheet. Any suggestions?

Also, on another workbook they would like to use the date of the tab as the
starting point in a series to assign a pickup number. ex. Tab is Thursday,
10-01. The number series would be 100101, 100102, 100103, etc. Then of
course, the next day would be Friday, 10-02 and the number series needs to be
100201, 100202, 100203, etc. Any suggestions on this??

Thanks!

 

[Luke M]

This formula will give you the sheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999)

You could then use various forms of using TEXT, DAY, MONTH functions to
create your series. Perhaps you could provide more examples as to what your
sheet names look like exactly?

 

[Cindy]

Ok, I must be doing something wrong here. I entered the following: (please
forgive me if I am overlooking the obvious, I'm still learning)

=MID(Cell("September 09 Daily Recap",B1),FIND("]", Cell("September 09 Daily
Recap", B1))+1,999)

I get a Value error. I have made sure the cell format that I want this in
(B1) is a Date type.

A few questions here. What does the "]" refer to and why 1,999? This is
just so I understand as opposed to just copying a formula.

Thanks!

This formula will give you the sheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999)

You could then use various forms of using TEXT, DAY, MONTH functions to
create your series. Perhaps you could provide more examples as to what your
sheet names look like exactly?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I have a macro that assigns the Day and Date of the month to each tab (Thanks
to the folks on this board) but I would like it fill the corresponding day
and date in a cell on each worksheet. Any suggestions?

Also, on another workbook they would like to use the date of the tab as the
starting point in a series to assign a pickup number. ex. Tab is Thursday,
10-01. The number series would be 100101, 100102, 100103, etc. Then of
course, the next day would be Friday, 10-02 and the number series needs to be
100201, 100202, 100203, etc. Any suggestions on this??

Thanks!

 

[Gord Dibben]

Do not use your own filename in place of "filename"

Enter the formula as posted.

For more on the why's and how-to's see Bob Phillips' site.

http://www.xldynamic.com/source/xld.xlFAQ0002.html

The +1 is there to tell the MID function which character to start at.

The 999 is there to tell the MID function how many characters to return.

The 999 is way more than you will need but covers all cases.

Generally 255 is used, which is enough since pathnames cannot be longer
than 255 characters.

For more on the MID function and its parameters, see help on MID

The FIND("]") tells Excel to return the sheetname after the workbook name.

When referring to a sheet on another workbook the syntax is

=[Book3.xls]Sheet2



Gord Dibben MS Excel MVP

Ok, I must be doing something wrong here. I entered the following: (please
forgive me if I am overlooking the obvious, I'm still learning)

=MID(Cell("September 09 Daily Recap",B1),FIND("]", Cell("September 09 Daily
Recap", B1))+1,999)

I get a Value error. I have made sure the cell format that I want this in
(B1) is a Date type.

A few questions here. What does the "]" refer to and why 1,999? This is
just so I understand as opposed to just copying a formula.

Thanks!

This formula will give you the sheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999)

You could then use various forms of using TEXT, DAY, MONTH functions to
create your series. Perhaps you could provide more examples as to what your
sheet names look like exactly?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I have a macro that assigns the Day and Date of the month to each tab (Thanks
to the folks on this board) but I would like it fill the corresponding day
and date in a cell on each worksheet. Any suggestions?

Also, on another workbook they would like to use the date of the tab as the
starting point in a series to assign a pickup number. ex. Tab is Thursday,
10-01. The number series would be 100101, 100102, 100103, etc. Then of
course, the next day would be Friday, 10-02 and the number series needs to be
100201, 100202, 100203, etc. Any suggestions on this??

Thanks!

 

[Cindy]

Gord, thanks for all the info. I will give this a try here shortly.

Do not use your own filename in place of "filename"

Enter the formula as posted.

For more on the why's and how-to's see Bob Phillips' site.

http://www.xldynamic.com/source/xld.xlFAQ0002.html

The +1 is there to tell the MID function which character to start at.

The 999 is there to tell the MID function how many characters to return.

The 999 is way more than you will need but covers all cases.

Generally 255 is used, which is enough since pathnames cannot be longer
than 255 characters.

For more on the MID function and its parameters, see help on MID

The FIND("]") tells Excel to return the sheetname after the workbook name.

When referring to a sheet on another workbook the syntax is

=[Book3.xls]Sheet2



Gord Dibben MS Excel MVP

Ok, I must be doing something wrong here. I entered the following: (please
forgive me if I am overlooking the obvious, I'm still learning)

=MID(Cell("September 09 Daily Recap",B1),FIND("]", Cell("September 09 Daily
Recap", B1))+1,999)

I get a Value error. I have made sure the cell format that I want this in
(B1) is a Date type.

A few questions here. What does the "]" refer to and why 1,999? This is
just so I understand as opposed to just copying a formula.

Thanks!

This formula will give you the sheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999)

You could then use various forms of using TEXT, DAY, MONTH functions to
create your series. Perhaps you could provide more examples as to what your
sheet names look like exactly?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


:

I have a macro that assigns the Day and Date of the month to each tab (Thanks
to the folks on this board) but I would like it fill the corresponding day
and date in a cell on each worksheet. Any suggestions?

Also, on another workbook they would like to use the date of the tab as the
starting point in a series to assign a pickup number. ex. Tab is Thursday,
10-01. The number series would be 100101, 100102, 100103, etc. Then of
course, the next day would be Friday, 10-02 and the number series needs to be
100201, 100202, 100203, etc. Any suggestions on this??

Thanks!