• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help using the search formula in additional worksheets

G

Gizmo369

New Member
I had previously posted a question in regards to sorting data and this formula below has been a lifesaver! However I've been trying my hardest and am now strapped for time and I can't seem to get this to work if I want to reference A2:A100 in another worksheet. Ever time I try it opens a box to open a document!(with the data I'm pulling I'm using column E but I doubt that matters). Please help :(

If you are wanting to count how many cells in a range have the text "1.3" you could do:
=SUMPRODUCT(1*(ISNUMBER(SEARCH("1.3",A2:A100))))
 
Hi, Gizmo369!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!

PS: I checked your previous thread and didn't find any clue regarding the issue that you're now experiencing.
 
Sorry that my explanation was unclear. I was stressed at the time and, for the record, I have read everything under the new users and the only thing that I did not do was put an example in because I was completely stuck and did not have time to create one.

This was a new question and it only had a reference to the formula which is why I posted a new thread (which is what you are told to do in the forums). I apologise if I've missunderstood anything.

What I was trying to ask was:
How can I modify this formula to to looked for "1.3" in another sheet in the workbook?

For the record, I have managed to answer my own question. I somehow mangled the formula when entering it and it caused it to create an open folder window instead. I have attached a document this time to show the final product of what I was looking for.

Thank you for your attempt to help me and sorry for wasting your time :)
 

Attachments

Hi, Gizmo369!

It's me who has to apologize now since reading your new post now more carefully and then reading again your first one, I should have answered you this:
=SUMPRODUCT(1*(ISNUMBER(SEARCH("1.3",'worksheet name here''!A2:A100))))

Now checking your uploaded file your worksheet seems to be ALL.

I have this question, that maybe helps you: did you already have that worksheet or you just built it manually (I didn't see neither formulas nor code) for this purpose?

Regards!

PS: It looks as if yesterday has been one of those days, at least for both of us.
 
Hi Gizmo369,

Try this one..

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{1.1;1.2;1.3;1.4}&"'!B2:B10"),"*"&B9&"*"))

where 1.1;1.2;1.3;1.4 are list of all the sheet's you want to count..
 

Attachments

No worries SirJB7! I do good work under pressure but my communication skills tend to fail me, lol. I ended up making the sheet this morning when I went back to try and figure out what I did wrong. I still have no idea really because when I mess the formula up it usually just throws an error message. ...Mind you, we use excel through a virtual system through work so I find that many feature tend to glitch. I would like to apologize for being snappy in my last post. =/

Debraj - I am confused as to where the formula is pulling the number's from. Could you explain this?

(I will be taking the excel school in January so hopefully I wont be so novice after that!)

PS: You guys are my heroes :3
 
You must log in or register to reply here.
Back
Top