• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

TAB NAME as YEAR DATE REF in CELL?

Stephan

Member
1st Date of Year using Tab Ref, not Cell Ref?

For example in Tab TOTAL Cell BF2:
=DATE(YEAR(BG2),1,1)

Hence how to substitute BG2 with TAB YEAR: 2020?

The cause reason is to automate Years Tabs Cell X39, which currently is manually edited date.

Cheers
 

Attachments

S. Das

Active Member
Stephan
Your question is not understandable.
what do you mean by "TAB YEAR: 2020"?
Also, I don't understand the term "Tab TOTAL".... Do you mean Worksheet "TOTAL" or anything other?

69352
 

Stephan

Member
2020 tab refers to year 2020
2021 tab refers to year 2021

Total tab is summary of these seperate years.

Formula in Year tabs calcs used/spent average apart from mot costs (£45+).

Tab: TOTAL
Cell: BF2
=DATE(YEAR(BG2),1,1)

Hence how change ref of BG2 to Tab of Year such as 2020.

Currently I do this manually as it is just 1 character in 1 year, an easy edit....
 

Stephan

Member
Or to make it much more obvious of the deliberate intention:

=IF(A6<=$BG$2,INDIRECT(TEXT($A6,"yyyy")&"!$Y$34"),"")

In tab TOTAL, cell C6. A6 indirectly refers to year then cell.

The obvious next thing to celebrate all going well is to do same with refering to tab name as year "without" cell ref.

Is this making intention more obvious to do what is to be done? It is wasn't necessary at all but nice to know how isn't it, but 1o1 things it takes few seconds to do.
 

Peter Bartholomew

Well-Known Member
I am not sure whether this is relevant to the discussion, but the formula
= MID(CELL("filename"), 1+SEARCH("]", CELL("filename")), 128)
will replicate the tab name in the formula cell.
 

Stephan

Member
I am not sure whether this is relevant to the discussion, but the formula
= MID(CELL("filename"), 1+SEARCH("]", CELL("filename")), 128)
will replicate the tab name in the formula cell.
Maybe I'm asking the wrong question, how about:

WORKSHEET TAB NAME as FORMULA DATE (YEAR)?

Your Formula suggested did do as suggested however it was unusable because:
= MID(CELL("filename"), 1+SEARCH("]", CELL("filename")), 128)
1. Not recognised as Date & can't be custom formatted as date.
2. When used on other Worksheets in same Workbook, then unusual side effect, they all reference last worksheet tab formula was last edited/used in.
3. Perhaps this formula is still of use, if can be "preceded" with:
01-01-YY (ie last 2 digits of worksheet name, they sorted it for the "Year 2000 Millennium Bug" into 4 digit to future proof but just 20th Century is my concern).

Guess somebody has knowledge of a Formula, to do this simple task as to preset Worksheet name as 4digit year as 1st day of that year? Doesn't sound like Mission Impossible to me, I've just not seen it yet....
 

Peter Bartholomew

Well-Known Member
Sorry, CELL is not something I use very often and I had forgotten some of its stranger behaviours. The function takes a second parameter that needs to be a cell on the sheet you wish the formula to reference; A1 would do since it defaults to the current sheet.
= MID(CELL("filename", yr), 1+SEARCH("]", CELL("filename", yr)), 128)
In the formula as shown, I have chosen to introduce a local name 'yr' on each sheet (using Name Manager) that refers to the cell that I have used to return the name from the tab as text. Since, in the present case, you know you wish to return the final 4 characters from the sheet tab, the formula reduces to
= RIGHT( CELL("filename", yr), 4 )

On your other point, the conversion of the year as text to a datevalue can be achieved with the formula
= DATE(yr,1,1)
 

Attachments

Stephan

Member
Right I'll try those.....

& also hard to dodge the velocity of this the previous Formula did actually work, the Worksheet Tab name just needed formatting like a date:

01-01 etc

However I found the impact of this making the purpose of each tab more vague on 1st impression as all start the same way & wouldn't want the embarrassment & confusion of wrong data entry, and not forgetting the anomaly of using same formula more then once on same workbook, hence will try above or just manual etc,
 

Stephan

Member
Sorry, CELL is not something I use very often and I had forgotten some of its stranger behaviours. The function takes a second parameter that needs to be a cell on the sheet you wish the formula to reference; A1 would do since it defaults to the current sheet.
= MID(CELL("filename", yr), 1+SEARCH("]", CELL("filename", yr)), 128)
In the formula as shown, I have chosen to introduce a local name 'yr' on each sheet (using Name Manager) that refers to the cell that I have used to return the name from the tab as text. Since, in the present case, you know you wish to return the final 4 characters from the sheet tab, the formula reduces to
= RIGHT( CELL("filename", yr), 4 )

On your other point, the conversion of the year as text to a datevalue can be achieved with the formula
= DATE(yr,1,1)
?? These all have errors in Excel 2003!! ??

I'll leave it in MANUAL EDIT for 1st DATE of YEAR, 01-01-YY, totally bullet proof.

No thanks to the Millennium Bug (ie a technological issue of outdated tech with 2 character/digit year going into another/New Century.... Aka Buck Rogers), the suggested formulas have "ricochet" effect, of creating more problems instead of minimising time needed.

The suggested Formulas have error of duplicating last tab ref'd when used more then once in same workbook in different tabs, hence the opposite of the purpose for seperate totals per "calendar year". And to create defined names, of same formula will just to same unwanted domino effect.

If was sceptical surmise somebody had foreseen use of this & had created a finally anomaly to hinder/prevent "being organised".

Good job I make "backup" of files before working on them, ever such a familiar lifestory of trivial things turning into vast onerousities. If had not have learned from this ever such regular occurence, then would have wasted a lifetime reworking something that was better previously!

Was it like that before? Like that?
Etc 01-01
 
Top