Vlookup that sums multiple worksheets based on two criteria

**jay_boogy** · 12-09-2010, 11:45 AM

I need to create a formula based on two criteria. I need it to look at each sheet tab's data and pull in the cumulative total.

There are currently 26 sheet tabs that may contain the criteria. There are three columns on each sheet tab. The criteria will be based on the first two columns. The third column contains the cost. I just want to add the cost wherever the criteria matches.

Please no VBA programming.

Thanks in advance for your assistance!

**NBVC** · 12-09-2010, 11:53 AM

How are the 26 tabs named?

**jay_boogy** · 12-09-2010, 11:56 AM

No consistent naming convention...ex. 700300 CR, 6100.3252, 657300 DR, etc.

The names are not long, but they are not consistent because that is how they come in.

**NBVC** · 12-09-2010, 12:06 PM

okay, then, create a list of those sheet names in a new column in the summary sheet... and then name that range TabNames.

Then apply formula like:

Please Login or Register  to view this content.

where A1:C100 on those sheets contains the data you want searched and summed.. adjust as necessary.

And A1, B1 on the summary contains your search criteria.

**jay_boogy** · 12-13-2010, 09:13 AM

Thanks for the quick response, I am going to try to see if I can get this work right now. I will respond back soon!!!!

**jay_boogy** · 12-13-2010, 03:22 PM

HELP...I am receiving REF# type error...

Currently the criteria ranges are in column X and column Y on the 26 sheet tabs (excluding the summary sheet). Column Z contains the amount that I am adding up. The summary sheet shows the criteria to meet. I placed the TabNames reference on the Summary sheet.

Any ideas on where I might I have messed up?????????

Please Login or Register  to view this content.

Thanks in advance for your assistance!

**NBVC** · 12-13-2010, 03:34 PM

You show the summing range in the last argument too.. should that be Y1:Y100 instead of Z1:Z100?

Please Login or Register  to view this content.

also go to Formulas tab, then click Name Manager and select TabNames and make sure the range is correct.

Make sure that each cell in that range matches exactly each sheetname it is supposed to reference... no extra spaces, etc.

**jay_boogy** · 12-13-2010, 03:43 PM

You are exactly right on the correction...

Please Login or Register  to view this content.

I will check the Name Manager now.

**jay_boogy** · 12-13-2010, 04:05 PM

I found out my issue, some of the sheet tab names have a period/decimal within the name.

When I take those sheet tab names out of the TabNames range, everything seems to work even though I haven't tried adding it up myself.

But, is there anyway that I could include those sheet tab names without taking out the period???

**NBVC** · 12-13-2010, 04:35 PM

Can't you enter those sheet names into the TabNames range with the periods in them so that they match exactly?

**jay_boogy** · 12-13-2010, 06:17 PM

I double checked and some of the sheet tabs lost its zero at the end. I had to format the TabNames as text and add the zeros back in. Everything seems to be working but I haven't had a chance to make sure it is adding correctly (will be doing that shortly).

Last thing: Is there a way to create a list of sheet tab names that is formatted as text and start the list after the second sheet tab in the workbook. The list can be placed on the summary page or on its own separate worksheet. Here is some different code I found, but I am unable to get any of the code to work in Excel 07 for me. Even if I did get any of it to work, the code returns all sheet tab names and probably doesn't format the names to text.

The below code is supposed to add a new worksheet “SheetList” and will list all the tab names there...

Please Login or Register  to view this content.

The below code doesn't add a new tab but list the worksheet names in the active worksheet...

Please Login or Register  to view this content.

You have been an excellent help so far so any assistance you can provide would be greatly appreciated. If I can get this last part then this would complete this thread!

**NBVC** · 12-14-2010, 08:56 AM

How about this one?

Please Login or Register  to view this content.

This puts the list in column AA starting at AA2... change as needed.

**jay_boogy** · 12-14-2010, 10:45 AM

Awesome, I did have to tweak it just a little. But I got it to work for me by using the following code:

Please Login or Register  to view this content.

This is fine but is there also way to make the data insert into its own sheet or should I just copy this code on a new sheet tab. Since I want the list to start after the second sheet tab shown, this code forces the list to start on the third row.

Is there anyway to still start the list on the FIRST ROW and starting naming the sheet tabs after the second sheet tab?

**jay_boogy** · 12-14-2010, 10:50 AM

I inserted a new sheet tab (first sheet tab now) and added the following code:

Please Login or Register  to view this content.

This works fine but it still skips to three rows before it starts the list???????

**NBVC** · 12-14-2010, 11:08 AM

So which sheets are supposed to be listed (i.e. from sheet number to sheet number) and which row is it to start listing in?

**NBVC** · 12-14-2010, 11:23 AM

Please Login or Register  to view this content.

This revision will start listing from the 3rd sheet (For i = 3 To sCount) to the last sheet and put them in column A starting at row 2 (ActiveSheet.Range("A" & i - 1)).

**jay_boogy** · 12-14-2010, 11:26 AM

1st sheet tab = New One I just created to list all of the sheet tab names needed for the formulas
2nd sheet tab = Summary Sheet (the one that references the sumproduct formula)
3rd sheet tab = Miscellaneous report/data (not used for any formulas just a sheet that provides some info)
4th through 29th (or however far it goes out) sheet tab = represent the sheet tabs that I need in the list "TabNames". Currently there are 26 sheet tabs but this number may grow or shrink. So next month there may be 20 sheet tabs with actual data or 29 sheet tabs.

**NBVC** · 12-14-2010, 11:47 AM

If you want list to start in A2, then:

Please Login or Register  to view this content.

If you want the list to start in A1, then:

Please Login or Register  to view this content.

Note: You should add a button that links to the macro, so that you can update the list on the fly.

**jay_boogy** · 12-14-2010, 12:46 PM

Perfect perfect perfect!!!!!!!!!! You are so awesome....thank you bunches!!!!!!!!!

**jay_boogy** · 12-14-2010, 12:47 PM

Is there anything i need to do to close this issue!!!!!

I already added to your reputation!!!!

**NBVC** · 12-14-2010, 12:59 PM

Great! You are welcome,

Just need to mark the thread solved:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Vlookup that sums multiple worksheets based on two criteria

LinkBack

Thread Tools

Rate This Thread

Display

Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Re: Vlookup that sums multiple worksheets based on two criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions