Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-12-2022, 05:26 PM
NLJ NLJ is offline Conditional Statement using data columns Windows 10 Conditional Statement using data columns Office 2016
Novice
Conditional Statement using data columns
 
Join Date: Jan 2020
Posts: 5
NLJ is on a distinguished road
Default 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!
Attached Files
File Type: xlsx Example data.xlsx (20.7 KB, 9 views)
Reply With Quote
  #2  
Old 03-14-2022, 02:22 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Statement using data columns Windows 10 Conditional Statement using data columns Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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","")
copied down and across.


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?
Reply With Quote
  #3  
Old 03-14-2022, 05:46 AM
ArviLaanemets ArviLaanemets is offline Conditional Statement using data columns Windows 8 Conditional Statement using data columns Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Maybe this?
Attached Files
File Type: xlsx ExampleData.xlsx (23.6 KB, 8 views)
Reply With Quote
  #4  
Old 03-14-2022, 10:34 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Statement using data columns Windows 10 Conditional Statement using data columns Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by ArviLaanemets View Post
Maybe this?
Arvi, check out the value in cell B8 of Quarters sheet!
Reply With Quote
  #5  
Old 03-15-2022, 12:01 AM
ArviLaanemets ArviLaanemets is offline Conditional Statement using data columns Windows 8 Conditional Statement using data columns Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

There was a typo in Quarters table - PrevLast for Q4 2023 must be 30.09.2023, not 30.09.2022!
Reply With Quote
  #6  
Old 03-21-2022, 04:51 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Statement using data columns Windows 10 Conditional Statement using data columns Office 2019
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

[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
Reply With Quote
  #7  
Old 03-21-2022, 07:58 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Statement using data columns Windows 10 Conditional Statement using data columns Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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",""))
Reply With Quote
  #8  
Old 03-21-2022, 03:06 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Statement using data columns Windows 10 Conditional Statement using data columns Office 2019
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you. I need a free weekend to understand the code and a further reading on the LET function is called for.
Reply With Quote
Reply

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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:16 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft