LinkBack URL
About LinkBacks
I try to setup something like this:
However I get #REF error. Any idea to fix this?
Please Login or Register to view this content.
I try to setup something like this:
However I get #REF error. Any idea to fix this?Please Login or Register to view this content.
Last edited by jackgan; 05-09-2014 at 03:54 PM.
Not real clear.
Are you testing that EVERY cell is <1000 or ANY cell is <1000?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I think I will change it to this for better understanding:Originally Posted by Tony Valko
And also can you show me a way to test if EVERY and ANY cell is <1000?Please Login or Register to view this content.
OK, you want to do a SUMIF across the sheets. It's a bit more complicated.
Are the sheet names really Sheet1, Sheet2, Sheet3, etc. ? If so, then we can build the sheet names directly into the formula. If the real sheet names are randomly named then you'll need to enter the sheet names in a range of cells and refer to that range.
P.S.
I'm getting ready to break for lunch so I'll be offline for a few hours.
Yah it probably will be SUMIF. The sheet names that I am going to use will be like Contact 1 all the way to Contact 20. I tested with the following and it works while changing the name from standard Contact 1 to Contact 20 into any sheet name according to the name.
Please Login or Register to view this content.
This formula will do a SUMIF A1<1000 across the sheets Contact 1 thru Contact 20.
=SUMPRODUCT(SUMIF(INDIRECT("'Contact "&ROW(INDIRECT("1:20"))&"'!A1"),"<1000"))
Note that all the sheets must already exist otherwise you'll get an error.
That will not work if I change the sheet name, however I do have a Summary sheet with range A1:A20 with the name I want to rename the sheet which will be done using macro, will that help?
My current situation is referring to this post. What I tried to do is to put A B C into separate sheet but still have the array working. I don't know if that can be done but if not then I probably just need to do it manually (end up with a long formula).
http://www.excelforum.com/excel-form...the-money.html
If the sheet names are in the range A1:A20, try this:
=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A20&"'!A1"),"<1000"))
It works if the sheet name is Contact1 to Contact20 (No Space). Is there a way to make it work with space in it?
Also can you explain why you need to put ' in that way? I know this might sounds stupid but I really curious, or it is just the way it is?
=SUMPRODUCT(SUMIF(INDIRECT(" ' "&A1:A20&" ' !A1"),"<1000"))
I think I had figure it out. The formula I use is as below. Thanks Tony Valko for the help!
Please Login or Register to view this content.
Good deal. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks