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

Seli

New Member
Joined
Oct 4, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

daregu

New Member
Joined
Jun 16, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Do you have a sample of what you're looking for?
 

Afro_Cookie

Board Regular
Joined
Mar 17, 2020
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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.

FormulaResult
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 $
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,393
Messages
5,769,824
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top