reference multiple sheets

[the6thplague]

hi,
I have an excel file with a lot of sheets in it. One sheet is named "main" and the other ones are named: 1, 2, 3, 4, 5, etc...

I want to put the following function in the "main" sheet:

=COUNTIF('1'!M2:M23,B1 & A2) + COUNTIF('2'!M2:M23,B1 & A2) + COUNTIF('3'!M2:M23,B1 & A2) + etc...

The question is, is there a way to somehow tell excel to run the function to sheets 1 to 31 without typing a very very long function like above?

Thanks a lot.
Eyal.

[DonkeyOte]

Eyal, conditional 3D summation/count is non-trivial.

John McGimspey outlines a variety of approaches: http://www.mcgimpsey.com/excel/threedsumif.html

Although the above relates to SUMIF the same logic holds true for COUNTIF.

Note: the SUMPRODUCT approach though elegant is inefficient and Volatile.

[the6thplague]

isn't there a simpler way? this looks very complicated... or maybe I don't understand it...

[JBeaucaire]

This involves adding a new function to your workbook. If you're OK with that, this would provide a notably simpler formula.

Here's a a UDF offered from here:
http://www.mrexcel.com/forum/showthread.php?t=36082#4

Please Login or Register  to view this content.

==========
How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.

The new COUNTIF() formula would then be:
=COUNTIF3D(M1:M32, B1 & A2,"1","2","3","4","5","6","7","8","9","10")[/QUOTE]

I've only entered 10 strings for sheet names, you enter all the ones you need.

[JBeaucaire]

Here's an alternate version that lets you enter the sheet names into a range of cells. Might be easier for you to maintain, up to you:

Please Login or Register  to view this content.

Possibly then used as:
=COUNTIF3D(M1:M32, B1 & A2, C1:C31)

[the6thplague]

Here's an alternate version that lets you enter the sheet names into a range of cells. Might be easier for you to maintain, up to you:

Please Login or Register  to view this content.

Possibly then used as:
=COUNTIF3D(M1:M32, B1 & A2, C1:C31)

Hi,
Thanks a lot! The first version works perfectly. The 2nd version would be easier but for some reason it doesn't work...

[JBeaucaire]

Post up your book with the UDF in it and it not working, I'll take a look.

[the6thplague]

Hi,
For some reason also, the first function only works until 27, but I have sheets going up to 31...

It says I have too many arguments. If you could find what's wrong with your 2nd function and fix it, it will really save me a looooot of time.

H E L P !


THANK YOU!!!!!!!!!!!!!!!!!!!!!1

[the6thplague]

Hi,
Attached is my excel file with the 1st function. The function is on the sheet "monthly report"

thanks

[JBeaucaire]

I thought you needed help with your implementation of the second macro. I don't see where you set up the sheet name range.

[the6thplague]

Hi,
The code should be on the monthly report under "intro" & "Lavrik". (I only need 1 example and I'll figure out the rest).

It should look up in the other sheets (1 to 31) for the combinatioin of words "Introlavrik" in m2 to m23.

Thanks a lot!

[JBeaucaire]

Did you examine the sample workbook?

[the6thplague]

yes i did but it's still not working.
Under "sheets" what should I write if I want sheets "1", "2", "3"?

=countif3d($M$2:$M$23,B$1 & $A2, Sheets)


should I write:

=countif3d($M$2:$M$23,B$1 & $A2, 1:3)

???

or something else?

thanks a lot!

[JBeaucaire]

Do you know what Named Ranges are? Look over in column AA and you'll see a list of sheet names 1-31. If you highlight all the numbers, you'll see "Sheets" appear in the Name Box (just to the left of the formula bar).

By naming a range of cell, you can use the name in the formula instead of a cell references. So the formula uses "Sheets" in the final parameter instead of the range of cells AA2:AA32. I suppose the cell reference is fine, too.

I'm finding the UDF I wrote is acting weird. For some reason it is declaring a circular reference when we try to use the column M, I'll have to work with it more when I get a chance. You can use it as is just skip column M on the Monthly Report (hide it).

Or, you can go back to using the original UDF, just break it up into two formulas since you can't have more than 27 array parameters in one function.

[the6thplague]

Hi,
I tried the last excel file you sent and it's not working. For some reason under "intro" & "Lavrik" it shows 1 and also under "guided" & "Lavrik" it always shows 1.

I cleared all the other sheets of data and only left data in sheet number 1.

Right now in the monthly report there should only be and intro for Lavrik, Darya, Anatoly and Andre.

I think that the first function you told me to use worked perfectly. It just couldn't handle more than 27 sheets. Can i just write "Sheets" like you did with the named references instead of writing "1", "2", "3"???? Or would that not work?

You also said I can split it, what did you mean?

I am kind of new to excel, thanks for all the patience!

[JBeaucaire]

1) a slight tweak to the current function:

Please Login or Register  to view this content.

2) Although I like trying to write applicable new functions, I really think the solution lies in using a more standard sheet design. Having 31 separate days of data that you want to analyze as a single data set is obviously cumbersome.

I would suggest you use a single data sheet for this. Add a new column A called "day" or "date" and put the 1-31 in there as you go down the sheet all through the month

Then your summary page can use simple sumif/countif and eliminate all of this. You can have an autofilter active on the data sheet so with the click of dropdown you can instantly be viewing only a certain, too, just like you would with separate sheets but without needing to have separate sheets.

In this sample sheet I've activated Excel's "List" feature on the dataset in the new sheet called "Data". The List feature bounds the dataset as group and offers an expansion row at the bottom. If you select a new day in that expansion row, Excel will instantly expand the dataset to permently include the new row and copy all the formulas and formatting to the new row without you having to do any of that. Very slick.

Use the dropbox in column A to filter the data anytime you want to see a smaller subset.

All the formulas on your Monthly Report sheet are simplified to match. Each colored section is a different formula.

I would find THIS data simpler to maintain by a longshot.

[the6thplague]

wow! your last post really blew me away! How do I do a thing like that? this is amazing!!!

Thank you!

[JBeaucaire]

The sheet I attached to the last post is pretty much ready to use. Since the UDF isn't needed, I would remove all the code so the sheet stops asking you about enabling macros. Be sure to delete the module, just emptying it will not be enough.

Then just use the sheet.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[the6thplague]

you misunderstood me. It works perfectly. I just needed to edit it exactly to my needs, but except of that, it's perfect! Thank you!

I just wanted to know for future references, how to make a thing like that. Do you have a link for a tutorial?

Thanks a bunch!

[JBeaucaire]

No, there's nothing particularly fancy in the individual columns of the final sheet, as a whole it may work slick, but individually you should be able to pick apart those formulas pretty easily.

You can read up about Excel "List" feature by pressing F1 in Excel.