Combining Vlookup and sum across pages

**ORC** · 11-30-2011, 05:25 PM

Hello everyone,

I've been attempting to create some formulas and have run into some issues (not surprisingly, since I'm here!) that I was hoping one of you might have the solution to. The workbook that I'm using contains roughly 15 pages of information and I'm looking for a way to summarize this information without using pivot tables (as they cause the file to crash due to size).

The attachment has two pages (#1 and #2 since I'm so original) with examples of the format of the information and a third with a template of how I'd like to summarize the information. The driving factor behind the summarization will be the location in column A on page #1 and page #2. The Summary includes a column (E) for the Total Payments however this is only a "nice to have" type of thing in case someone has an easy solution. The information filled in is all just for show so if the dates make absolutely no sense, please just pretend that it's possible to go back in time or something!

I've got a vague idea as to what I need to do but I haven't played around enough with these types of formulas to have made much headway.

Thank you for taking the time to read through this and any assistance would be greatly appreciated.

~ORC

**NBVC** · 11-30-2011, 05:56 PM

If your sheets are truly named with such a pattern of consecutive numbers, then try something like, in B2:

=SUMPRODUCT(SUMIF(INDIRECT("'# "&ROW(INDIRECT("1:2"))&"'!A3:A22"),$A2,INDIRECT("'# "&ROW(INDIRECT("1:2"))&"'!D3:D22")))

copied to C2 and change the D3:D22 to E3:E22, then copy to next column and adjust again, etc...

Then copy down.

If not in that pattern, have a look here for help: http://www.xl-central.com/sum-single...le-sheets.html

**ORC** · 11-30-2011, 06:35 PM

Hello NBVC,

Thank you for your response to the question.

Unfortunately the tabs are not named in the same way as the file I had loaded as the example.

As for the link that you supplied, I attempted to re-create the example but ran into a few problems and could not get that one to work. The result of the formula (which I copied and pasted from the link) brought up a 0 for A and #REF! for B, C, and D.

Before I go any further with this, does the formula that you are suggesting take a great deal of time to calculate normally? The file that I am working with has 15 sheets of information that I need to pull from but about 40 sheets in total with a number of graphs, formulas, etc. If it is not possible to run a formula of this sort without putting large constraints on the file, perhaps I'd be better off just doing a manual report.

Thanks again for the suggestions!

**NBVC** · 12-01-2011, 08:49 AM

I am not sure the formula itself is that processor intensive. Only that it does include the volatile function, INDIRECT, which forces recalculations everytime you do anything in the workbook, even if not directly linked... so that might slow you down, especially if you have a lot of things going on....

You will obviously have to adjust the formula to your needs, and create your own list of exact tab names, all the tab names also have to exist.

If you need help, post a more representative example workbook.