Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

excel conditional formatting

Status
Not open for further replies.

keithinoz

Technical User
Dec 21, 2005
98
AU
In the attached simple Excel file, in column b is the monthly data and column c the YTD data. I want to "hide" the YTD duplicate values so that table looks like the red version. I tried conditional formatting using the duplicate values and the formatting by changing the font colour to white but that whites out all the numbers. Help please
 
 https://files.engineering.com/getfile.aspx?folder=205c7955-9e74-421b-9afc-99a8def30588&file=example.xlsx
Hi, Keith.

YTD? Fiscal starts on July 1?, regardless the day of week? Or is YTD based on actual year, regardless of week's start?

Also its odd to just use text months in a spreadsheet rather than actual dates FORMATTED to display months.

So wouldn't you want to "hide" future months YTD? Of course you couldn't accomplish that without actual dates in column A.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
In Australia, the financial year starts 1 July. RE showing the months, it suits to me to see them all
 
[pre]
Amount YTD in Column A
1-Jul 23 23 A2: Actual YTD Start Date
1-Aug 23 A3: [tt]=DATE(YEAR(A2),MONTH(A2)+1,1)[/tt] copied down
1-Sep 23
1-Oct 23
1-Nov 23
1-Dec 23
1-Jan 23
1-Feb 23
1-Mar 23
1-Apr 23
1-May 23
1-Jun 23
[/pre]

SELECT C2:C13
Open CF Wizard
Select FORMULA
formula: =YEAR(A2)&TEXT(MONTH(A2),"00")>YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")

[URL unfurl="true"]https://res.cloudinary.com/engineering-com/raw/upload/v1626841902/tips/example_2_zz2k6l.xlsx[/url]

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
Appreciate that but it doesn't solve my original problem of not wanting to see all the "23" othe rthan the one for July
 
Did you download the workbook? That's what you'll see.

Edit: So sorry my CF formula was incorrect above, but correct in the workbook I uploaded.

I corrected the formula above.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top