Hi guys. Please take a look at the attached file....
Is it possible on sheet 2, to type in the name of the comapny and all the details for each individual on sheet one pop up on screen? i dont mean in a pop up, I mean somewhere specified in cells on sheet 2?
So if I type Shelter in the yellow box on sheet 2, then all the details come up for the 4 employees that work there? including email and phone etc....
Thanks in advance...
See attached.
Note: You will have to adjust the ranges, $A$2:$A$10 and $B$2:$B$10, in C3 to suit, then confirm the formula with CTRL+SHIFT+ENTER not just ENTER and copy the formula down as far as necessary and to the right as far as necessary.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thanks!!! That's great.
I've had a look and want to add another sheet which searches by Surname. I've copied it and messed about with the formula but can't quite see which parameters to change? So basically I am swapping the name for the company. So if type in a surname then the company and rest of details will come up? Hope that makes sense?
Thanks
See Sheet3 in attached....
Need to change range in Match formula
Need to adjust Index range
Need to adjust If condition range
Thanks for that - I can see now where I have to make adjustments.
Thanks for all your help!!!
You're quite welcome!
OK I am confused.
If I want to add a sheet to search by first name, surely I amend:
=IF(ROWS($B$1:$B1)>$B$3,"",INDEX(Input!A$3:A$11,SMALL(IF(Input!$C$3:$C$11=$C$3,ROW(Input!$B$3:$B$11)-ROW(Input!$B$3)+1),ROWS($B$1:$B1))))
to
=IF(ROWS($B$1:$B1)>$B$3,"",INDEX(Input!A$3:A$11,SMALL(IF(Input!$B$3:$B$11=$C$3,ROW(Input!$B$3:$B$11)-ROW(Input!$B$3)+1),ROWS($B$1:$B1))))
But it throws up nothing? What am I missing out. They're the only variables that are changed?
You have to also change the formula in B3 to =COUNTIF(Sheet1!B:B,C3)
where Sheet1!B:B contain the first names.
Doh!!! Of course....
Cheers!!!
One last bit of help needed...
=IF(ROWS($B$1:$B1)>$B$3,"",INDEX(Input!A$3:A$11,SM ALL(IF(Input!$C$3:$C$11=$C$3,ROW(Input!$B$3:$B$11)-ROW(Input!$B$3)+1),ROWS($B$1:$B1))))
In this formula if I wanted C3 to equal data in columns A and/or I (so it's basically searching both columns) is this possible? And do I have to change the formula in B3?
Thanks
Last edited by neilcarden; 07-06-2007 at 05:36 AM. Reason: mistake
Hi,
To get matches from column C or I, you would change your formula to:
and your formula in B3 would change to:
Please Login or Register to view this content.
Please Login or Register to view this content.
I dont know what's going wrong!!!!
Can you have a look? It works to a certain extent but then not.....
see attached
Your Input range starts at row 2 and therefore your Sumproduct formula in B3 must start at row 2:
=SUMPRODUCT((Input!$B$2:$B$11=$C$3)+(Input!$I$2:$I$11=$C$3))
Oh cripes - keep forgetting about that.
Do you know why i'm getting all those results when there is nothing in the company C3?
As shown in example.
Sorry to keep hassling you over this.
SORRY - wrong example....
http://www.neilcarden.co.uk/example.zip
Last edited by neilcarden; 07-06-2007 at 09:40 AM. Reason: wrong
...because it is now searching for blanks in columns C and I.... and matching the data for those items.
try changing the Sumproduct() formula to:
Please Login or Register to view this content.
If I change to that it doesn't show anything when I type in a company?
:s
Sorry...my mistake.
Change back the Sumproduct formula to:and adjust the IF() formula to:Please Login or Register to view this content.
the red part is new.Please Login or Register to view this content.
Woohoo. Spot on!!!
Thanks for all your help mate!!
Great. Finally!
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