I'm having problems creating a summary cashflow spreadsheet that looks up a couple of references - a date (which is no problem) and a different spreadsheet name (which is the problem).
I have individual provider information on separate sheets (agreements, invoices, and individual provider cashflows).
I am trying to collate this information on a Summary cashflow spreadsheet.
Below is an image of the summary table with the formula that currently works, however the worksheet name (provider name, in this case, "EcOz") has to be typed in manually for each cell in that row for the correct results. It then uses VLookup to return the value for the corresponding month in the correct worksheet.
An image of the tabs is also provided - there is a separate worksheet for each Provider.
A typical table from each Provider sheet is shown below. This collates monthly expenditure (actual is greyed, forecast is yellow) to provide a cashflow for the individual service provider.
Ideally, I would like the cells in the Cashflow Summary table to lookup the Provider name referenced there, find the corresponding worksheet, and pull in the corresponding data from the individual provider table. NB. I can't just use a simple INDIRECT function (I think!) as the start dates for the individual cashflow tables vary. ie in "Ecoz" above, start month is Dec-15, but for Cardno (not shown), start month is Oct-15.
I'd appreciate any help/ideas offered!