Pull specific data up to end of last month-end

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
Ok, in my mind this is a job for an Excel super-sleuth. I keep losing myself in the the logic to get to a working answer.

In my screenshot, what formula could I use to return the YTD % of goal as of the end of the last month completed.

Details:
Everything is all in 1 workbook here. The range on the left is SHEETA & contains monthly goal numbers. The range on the right is a seperate sheet called "101 YTD Orders Report", & has a few random sales numbers which took place on certain dates. On the main home page of my sheet (Call it "MAINSHEET"), I would like to add all the "Total Sales" numbers from the sheet/range on the right which occured as of the last month completed (since today is 3/10/21, that would mean all numbers up to/ending February 28th). I would like to take that number and divide it by the total of all months for that store in the left sheet/range which have already occured and ended (again, in the case of today, that woud be Jan & Feb). Keep in mind that there are tabs at the bottom of the sheet which contain this same into (on the right side below) for each and every store. This tab is 101 YTD Orders Report, They are all named like that, but have a different 3 digit store number at the start of the sheet title.

How would I do such a thing?

1615429755769.png
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,144,369
Messages
5,723,952
Members
422,528
Latest member
IMK

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