0

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.

Cashflow Summary and worksheet tabs

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.

Provider cashflow for "EcOz"

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!

1

You can try Indirect with Concatenate like the same formula you are showing in C32 Cashflow Summary sheet

=IF($B32="","",IFERROR(VLOOKUP(C$30, INDIRECT(CONCATENATE($B32,"!B44:C58")),2,False),0))
  • Thanks @SuperSam! this is pretty much exactly what I did - although without the concatenate function. The formula that I started with wasn't far off the mark - I ended up using INDIRECT function - ie the following: =IF($B33="","",IFERROR(VLOOKUP(C$32,INDIRECT("'"&$B33&"'!$B44:$C58"),2,FALSE),0)) – Dan Mar 20 '16 at 23:23
  • This still requires the user to punch in the provider's name in column B, but that is ok as there shouldn't be too many providers for each project. – Dan Mar 20 '16 at 23:25
0

Try your formula using Lookup instead of Vlookup, Vlookup needs the first column to be sorted in order to find the exact value or include False

  • This is not true, vlookup() works with unsorted data if you specify FALSE for the fourth argument. This also has nothing to do with the OP's question. – Kyle Mar 18 '16 at 12:42

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.