How to fill in multiple columns in a table based on one drop down menu choice with "if" conditions

[Seli]

I am trying to fill in data in multiple columns of a table based on a single selection from a drop down menu created through data validation. I have the first column complete (this is finance/accounting) with populated sales per week, but I need the next column to populate the "current period to date" based on the selected week (P01 Week 1; P01 Week 2; etc...). My data pull is from a separate tab in the spreadsheet that pulls in all the data from a sales cube in SmartView/Essbase database into a "Summary" tab that is easy for other non-finance/accounting folks. How do I get the data range to fill in other columns based on the chosen week of the period which is chosen in the drop down menu to fill in the following columns: Current Period to date; Current Quarter To Date; Current Year To Date? I used Xlookup for my "Current Week" column and it works great, but I just can't figure out the other three columns.

 

[daregu]

Do you have a sample of what you're looking for?

 

[Afro_Cookie]

I am trying to fill in data in multiple columns of a table based on a single selection from a drop down menu created through data validation. I have the first column complete (this is finance/accounting) with populated sales per week, but I need the next column to populate the "current period to date" based on the selected week (P01 Week 1; P01 Week 2; etc...). My data pull is from a separate tab in the spreadsheet that pulls in all the data from a sales cube in SmartView/Essbase database into a "Summary" tab that is easy for other non-finance/accounting folks. How do I get the data range to fill in other columns based on the chosen week of the period which is chosen in the drop down menu to fill in the following columns: Current Period to date; Current Quarter To Date; Current Year To Date? I used Xlookup for my "Current Week" column and it works great, but I just can't figure out the other three columns.

If I understand what you're asking for correctly, a few columns with some formulas should do the trick. I'm not sure about the period to date though.

	Formula	Result
date	=today()	1/11/2022
current week	=WEEKNUM(C2)	3
Quarter identificaiton	="Q" & ROUNDUP(MONTH($C$2) / 3,0)	Q1
PTD
QTD	=sumifs(data,Quarter field,C5)	QTD $
YTD	=sumifs(data,date,C1)	YTD $