Forum Discussion
YTD on Excel Fuel Reconciliations
I am creating a Week by Week spreadsheet to monitor Inventory in our businesses in-ground storage tanks.
Certain requirements are needed to make this legal for auditing.
As such I need a YTD way to evaluate inventory, my problem is I have YTD looking at 53 other sheets for information in one spot, but my weekly section it reads from is always right on 1 page but wrong on another page until I finish working on it because it pulls information from the previous week. (it requires continuous information to show a true picture. What I want is the spreadsheet to not pull info from the next week until it is either complete or meets enough criteria to show a clear picture.
To keep it simple, I have included week one in the attached, YTD should match what Week 1 Rec matches. then once Week 2 is in then it should pull both Week 1 and Week 2 data, then Week 3 and so on. Im not sure if a macro might be more appropriate for this, however I have one Macro coded in right now, that is a =prevsheet() - cant upload a macro sheet so its coming to you without it
I looked for hours and couldnt find a simple solution, any advice or if someone can clear this up for me that would be great.
Use a separate block of cells that have formulas to gather the data for interest from the sheet, using this kind of logic:
=IF(cell with criteria for completed sheet,value of interest,0)
like
=IF($Z$1,A10,0)
where Z1 has a formula that returns FALSE until the criteria is met, when it returns TRUE.
For example
=AND(Q10<>"",Q20<>"",Q30<>"",Q40<>"")
Use as many of the IF formulas as needed to pull all of your data.
Just as an aside - you would be much better off using one of your sheets (as a report) and a sheet that works as a database, where one row is filled for every day, and has all the information that you would normally fill out for a day's area. Fill the report sheet with formulas to extract the data required from the database, based on a key value in a cell, like the first day of the week of interest's date. The database would allow you to filter, subtotal, and use a pivot table to extract other reports, which is the true power of Excel.
