#1
|
|||
|
|||
Conditional Statement using data columns
Hello - excel experts, I'm hoping you can help me. On a quarterly basis I do a pivot table from files I combine to monitor and forecast assignments (dates change frequently and there are new vendors added and removed). I manually add an "x" into the quarters where a vendor is "active" (the quarter is between the start and end date). I should be able to set an IF statement to populate this , but haven't been able to figure this out. Can anyone help? I've included a simple sample to demonstrate what I want to fill.
I've just made a linked connection to my sharepoint list so data is automatically updated to make this less burdensome, so if I can figure out a wat to fill the forecasting, this will save me time. Appreciate the help! |
#2
|
||||
|
||||
Test thoroughly (it might be a day out), in E2:
Code:
=IF((MIN(Table13[@[End]:[End]],DATE(RIGHT(Table13[[#Headers],[Q2 2022]],4),MID(Table13[[#Headers],[Q2 2022]],2,1)*3+1,0))-MAX(Table13[@[Start]:[Start]],EDATE(DATE(RIGHT(Table13[[#Headers],[Q2 2022]],4),MID(Table13[[#Headers],[Q2 2022]],2,1)*3+1,0),-3)))>0,"x","") Most of the length of the formula is extracting real dates from the table headers. It could also be shorter if you're using Office365 - are you? |
#3
|
|||
|
|||
Maybe this?
|
#4
|
||||
|
||||
Arvi, check out the value in cell B8 of Quarters sheet!
|
#5
|
|||
|
|||
There was a typo in Quarters table - PrevLast for Q4 2023 must be 30.09.2023, not 30.09.2022!
|
#6
|
||||
|
||||
[QUOTE Most of the length of the formula is extracting real dates from the table headers. It could also be shorter if you're using Office365 - are you?[/QUOTE]
Hi P45Cal. What would be the formula if one is using Office 365? Marcia |
#7
|
||||
|
||||
Shame the op NLJ can't be arsed to say thank you, a shorter version in 365 might be:
Code:
=LET(HdrDte,Table13[[#Headers],[Q2 2022]],dt,DATE(RIGHT(HdrDte,4),MID(HdrDte,2,1)*3+1,0),IF((MIN(Table13[@[End]:[End]],dt)-MAX(Table13[@[Start]:[Start]],EDATE(dt,-3)))>0,"x","")) |
#8
|
||||
|
||||
Thank you. I need a free weekend to understand the code and a further reading on the LET function is called for.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional formatting using specified range instead of With Selection statement | slouw | Excel Programming | 3 | 01-26-2019 06:24 AM |
If Statement for Outlook VBA: Time & User defined columns | Son | Outlook | 0 | 10-02-2015 11:02 AM |
Conditional Formatting/If Statement with Dates | AndrewEnos | Excel | 2 | 07-22-2014 07:34 AM |
Conditional Formatting for columns | MattMurdock | Excel | 1 | 08-09-2012 10:03 PM |
Conditional Formatting to Hide Rows or Columns? | sczegus | Excel | 0 | 09-26-2006 04:17 PM |