Failed searches on this topic or wrong wording in my search
How can I gather data in the same columns in multiple worksheets
search range is Column G in multiple worksheets
criteria will be assigned in a separate worksheet
range to sum is Column J of all worksheets matching criteria.
Last edited by MARKSTRO; 01-12-2012 at 01:40 PM.
See it you can get there with this...
http://www.ozgrid.com/forum/showthread.php?t=27306
or
http://www.mrexcel.com/forum/showthread.php?t=520020
HTH
Regards, Jeff
Here is what I want the function to look like.
=SUMIF(SRM:ESC!G1:G100,$J$5,SRM:ESC!J1:J100)
I get a #value! error as it is
I tried Sheet1:Sheet21 in my first attempt, no joy.
Sheet 1 is SRM and Sheet 21 is ESC.
List your sheets in a column and name it SheetList then you can do...
Sumif cannot be used across sheet in a 3D manner that is why it is wraped in Sumproduct which can handle an array...
Please Login or Register to view this content.
Thanks Jeffrey, Ok, more lessons.
List sheets in a column and name the range they're in as SheetList I know, do I use Sheet 1 or the actual name of the sheet.
It does comes up all Caps when I name it for one, will that cause problems.
I am not sure how to reference the SheetList range in the formula you provided, I see the flow here but not how to make it work.
I have tried to attach the spreadsheet, every time I attempt, Excel locks up and no upload at all with all Excel files shut down.
Not sure if the formula I inserted in the sheet is causing the problem or not.
Thanks for all your help.
Only way to help you is upload the workbook.
I'm gonna reboot the system and retry the attachment, still won't work
Not sure what attachment you are referring too, but try this example...
Last edited by jeffreybrown; 01-11-2012 at 07:58 PM.
Is there a size limit for a file upload?
I can attach a smaller file, just not the one regarding our issue and it still causes Excel to stop working when I do attempt the upload.
For: xls, xlsx, and xlsm it is 1,000.0 KB
For a zip file it is 9.77 MB but I don't suggest a file that big.
Can't you scale the example down to what is relavent or how did my example help you?
Our messages crossed, let me try and get back to you, Thanks.
I expanded the SheetList down to Sheet21 and entered my criteria in J5. I now get a #ref! error.
My SheetList range is now K5:K25, I copied your formula from the thread right into I5 of my sheet.
All the cell references in the formula appear to be off when I evaluate the errors.
Hi MARKSTRO,
I am not all that good at being able to diagnose a problem like this without the workbook in front of me, but a #REF! error is due to a cell reference that is not valid. Have you checked all of the sheets and the ranges to make sure there is nothing out of place.
http://office.microsoft.com/en-us/ex...010066250.aspx
I have asked a couple of times with no answer, but have you looked at the example I posted?
Sometimes it is best just to do a Sumif on each individual worksheet and then you can perform a Sum across the worksheets.
or
Make two new worksheets named first and last. Put the first tab at the beginning of the workbook and the last as the last.
Now create a Sumif in an open cell on each worksheet and then you can Sum across the spreadsheets.
I updated the posted example...
Jeffrey, yes I did look at your example, tried to use it in my sheet and got the #REF! error. My sheet is too large too attach.
I will try your example in another worksheet and try your other solution.
One observation, I have renamed the 21 sheets we are summing, when I insert another worksheet it is numbered Sheet1. Will that throw off the function?
Should I put the actual sheet names in the SheetList Range?
I will post back when I can.
Thanks,
Yes. Example: you have 5 sheets and they are Jan12, Feb12, Mar12, etc., you would put these five sheet names in a column and then name that column SheetList to match what is in the formula.
My guess is you are getting the #REF! error becasue the sheets names are not matching the named range.
After looking at the example you will see in column K, K5:K7, I have the names of all the sheets in the workbook that I want to sum from. I highlighted K5:K7 and in the Name Box (right about column A) and entered SheetList and hit enter.
Now select Ctrl + F3 and the Name Manager dialogue box will appear. You should now see SheetList as a named range. Hope this helps further
Ok, problem solved.
My basic knowledge of VBA and how to build formulas is lacking.
I renamed the SheetList Range with the actual named sheets vs. Sheet1:Sheet21 to make it work, I misinterpreted how to enter the data in the SheetList range.
The sumif across worksheets using First:Last is a much more easily taught formula I can use with fellow coworkers. Thanks.
Sure glad you got it working and you're welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks