blueice2627

New Member
Joined
May 21, 2018
Messages
10
Hi All,

I'm looking for a way (if possible) to allow my filters in a pivot table be based off of an "if" statement or something similar. The data that i'm pulling from is a little complicated to write out... but here goes nothing.

I work at a hotel and every night there is a snap shot of what we have booked for the future. Because it is the future only, it doesn't show were yesterday finished so the numbers are accurate for the future and that is it. I have another table that puts all my past dates together with the numbers that the day finished with. I have a column called "snap date" to show what day the data was "snapped" from.

To put some numbers out there to demonstrate:

SnapDateRooms
04/10/19
04/10/19
8
04/10/19
04/11/19
10
04/10/19
04/12/19
11
04/10/19
04/13/19
3
04/11/19
04/11/19
10
04/11/19
04/12/19
12
04/11/19
04/13/19
5
04/12/19
04/12/19
13
04/12/19
04/13/19
8
04/12/19
04/14/19
7

<tbody>
</tbody>

So here is what i'm trying to do. I want to see the state of the hotel at any given "snap" date. If i put the snap date to 04/11/19, i will get a return of 04/11 with 10 rooms, 04/12 with 12 rooms and 04/13 with 5, where if i put 04/12 in the snap, I'll only get 04/12 at 13 roooms 04/13 at 8 Rooms and 04/14 with 7 rooms.

The problem with that is, if i want to know my totals month to date, the only way to get yesterdays final number is to go to yesterdays number filtered to that days snap date. Essentially, i want to be able to select a snap date of 04/12 and receive 04/10 with 8 rooms, 04/11 with 10 rooms along with 04/12 with 13 rooms, 04/13 with 8 rooms and 04/14 with 7 rooms.

The only thing i can think of doing at this point is having it so that when i import the data for the report with the days final numbers, have it update the "snap date" of all the past data to "today" but i can be a data purist at times so i prefer to not mess with the original data unless i absolutely have to.

Hope that makes sense! Let me know if i need to clarify more.

Thanks,
Craig
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top