Return total amount values based on multiple criteria

[MyStix01]

Hi, can someone help me on what formula to use in cell C4:BB6 under MASTERFILE on my attached file. I want to modify it in such a way that the the formula is based on the date headers and the rows that shows "Total" on the sheets.

I do not want to simply reference a cell since the row numbers may change from time to time, and so instead I want my formula to be able to reflect the total amount for each week ending based on the dates and the row that has a row header of "Totals".

Can you also help me on what conditional format formula should I use in sheet FORECAST - CONSTR. What I want to happen is for those rows to be highlighted in blue fill if the vlookup does not return a value for start date. And to highlight in blue with red font those cells in which the vlookup return no value and thus the dates were instead base on the forecasted days of return.

Appreciate you help! Thank you so much!

[queuesef]

IT appears that you need data from 3 tabs: 1."Forecast-Constr" (which gives you 'Cash Inflows' and same dollar amount for 'Construction' 2. 'Cash Outflow' 3. 'Pre Construction'. If you could summarize those 3 tables weekly totals(Macro?) and create a pivot table based on the Date, you would have all the data. Pivot table:

1. ForecastConstr table summarized fields: Date; Total & 'ForecastConstr.
2. 'Cash Outflow' table summarized fields: Date; Total & 'CashOutflow'.
3. 'Pre Construction' table summarized fields: Date; Total & 'Pre Construction'
I Transposed the data (see below each section's data)
The Pivot table can be manipulated into your format to include the duplicated value for Construction.
(it really looks more like a job for a database like access, but you can use Excel)


good luck Mon

[MyStix01]

Hi queuesef,

Uhm, actually I just want my Masterfile to be auto populated with the total amounts for each week from the corresponding sheets. My current formula is just referencing the totals which is somehow okay for me. But instead of me referencing the start cell in column C and dragging it across the sheet, I want it to instead be able to capture the total amount already with me not having had to do anything. Thus the formula in my Mastefile should be able to detect the row "Totals" and return the total amount for that week for the sheet. Preconstruction in Masterfile should reflect the totals for each week in the sheet FORECAST - PRE CONSTR. Construction is for FORECAST - CONSTR. and Cash Outflows is for CASH OUTFLOW.

I hope I made it clear. Thank you so much!

[queuesef]

You can also use a combination of INDEX & Match to lookup the total. Or you could write VBA code to get last value from the Date you are looking up.