Creating Search Box in Excel

I am trying to create a search box feature in Mircrsoft Excel using the instructions I found online below:

1. Select cells "C2:E2" and click on "Merge & Center".

2. Select all the cell around the merged cells and fill it with black color.

3. Click on "B2". Change font color to white, font size to 12 and make it bold then type "Search".

4. Select cells B5 to F5, hold Ctrl and select C6:F6, C7:F7, C8:F8, C9:F9, C10:F10, C11:F11 and C12:F12 and click on "Merge & Center".

5. Fill with color as shown in the picture above.

6. Click "B5" hold Shift and click on F12.

7. Click on "Thick Box Border" from the border options. Displaying Search Result

8. Go to Sheet 2 in the same workbook.

9. Click on the formula tab and then "Define Names".

10. Create the following named ranges i.e.) Last Name, First Name, Date Will Signed, Witnesses, Date Will signed out, and Lawyer - (I changed the text to fit my situation)/

Sheet2.pdf

Then I typed in the following formulas in "Refers to:" text box respectively

"=OFFSET(Sheet2!$A$2,0,0,COUNTA(Shee2!$A:$A)-1)"
"=OFFSET(Sheet2!$B$2,0,0,COUNTA(Shee2!$B:$B)-1)"
"=OFFSET(Sheet2!$C$2,0,0,COUNTA(Shee2!$C:$C)-1)"
"=OFFSET(Sheet2!$D$2,0,0,COUNTA(Shee2!$D:$D)-1)"
"=OFFSET(Sheet2!$E$2,0,0,COUNTA(Shee2!$E:$E)-1)"
"=OFFSET(Sheet2!$F$2,0,0,COUNTA(Shee2!$F:$F)-1)"

Formula Box

Creating Formula To Enable Search:

*This all worked fine until I got to the instructions below ---

Searc Box Formula
1. Go back to "Sheet1".

2. Click "C6" and type "=IF($C$2="", "", $C$2)"      *****This worked******

3. Click "C7" and type "=IF($C$2="", "", INDEX(FIRST_NAME,MATCH($C$2,Name,0)))"

4. Click "C8" and type "=IF($C$2="", "", INDEX(DATE_WILL_SIGNED,MATCH($C$2,Name,0)))"
 
5. Click "C9" and type "=IF($C$2="", "", INDEX(WITNESSES,MATCH($C$2,Name,0)))"
 
6. Click "C10" and type "=IF($C$2="", "", INDEX(DATE_WILL_SIGNED_OUT,MATCH($C$2,Name,0)))"
 
7. Click "C11" and type "=IF($C$2="", "", INDEX(LAWYER,MATCH($C$2,Name,0)))"
 

*The formulas above did not work except the search box did pull over the Last Name information from Sheet 2, but for the rest of the information I require from Sheet 2 I only get a #VALUE! error as you can see in the below image:

Sheet-1.pdf
kimhanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zack BarresseCEOCommented:
Hi there,

The part of the formula which is failing is this part...

MATCH($C$2,Name,0)

Open in new window


You're saying here, find an exact match of what is in C2 in the named range 'Name'. Oddly enough you shouldn't be getting a #VALUE! error, but an #N/A error.

Try this in it's own cell...

=MATCH($C$2,Name,0)

Open in new window

What is the return?

Try this in it's own cell...

=INDEX(FIRST_NAME,MATCH($C$2,Name,0))

Open in new window

What is the return?

The error message is odd indeed and slightly alarming. There is a reason for it as well. What does the error message say? (See the little box in the corner of the cell when you select it?)

Also, is it possible for you to post a sample/copy of the file, without any personally identifiable information (PII)?

HTH

Regards,
Zack Barresse
0
kimhanAuthor Commented:
Attached is the file for your review.
Copy-of-KJF-WILL-LOG.xlsx
0
Zack BarresseCEOCommented:
Your named ranges are what's giving you errors. Hit CTRL + F3 to open the name manager (or found on the FORMULAS tab) and, as an example, let's look at the WITNESSES range.

It refers to:
="""=OFFSET(Sheet2!$D$2,0,0,COUNTA(Shee2!$D:$D)-1)"""

Open in new window

You don't want it surrounded in quotes like that. Also, look at the second worksheet reference. It's like this in almost every named range you have. Instead of referencing "Sheet2", you are referencing "Shee2", with a missing "t", and that sheet ("Shee2") doesn't exist. The above named range should look like this...

=OFFSET(Sheet2!$D$2,0,0,COUNTA(Sheet2!$D:$D)-1)

Open in new window


Update all of your named ranges to look like this.

In your sample file you also have not defined the range "NAME". I'm not entirely certain where this range should be. It appears as though yo uwant to type a last name in C2 of Sheet1, but if that were the case why wouldn't you just use "LAST_NAME" instead of "NAME"?

Once you do that and update your named ranges your formulas will work as desired.

To be clear, this is a list of what your named ranges should be...

DATE_WILL_SIGNED:
=OFFSET(Sheet2!$C$2,0,0,COUNTA(Sheet2!$C:$C)-1)

DATE_WILL_SIGNED_OUT
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1)

FIRST_NAME
=OFFSET(Sheet2!$B$2,0,0,COUNTA(Sheet2!$B:$B)-1)

LAST_NAME
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1)

LAWYER
=OFFSET(Sheet2!$F$2,0,0,COUNTA(Sheet2!$F:$F)-1)

WITNESSES
=OFFSET(Sheet2!$D$2,0,0,COUNTA(Sheet2!$D:$D)-1)



As another option to these named ranges, you could ignore them all and use a table. Go to Sheet2, select your data (including headers), press CTRL + T, ensure the headers checkbox is checked, click OK. Use this formula for LAST NAME on Sheet1 and copy down...

=IFERROR(IF(VLOOKUP($C$2,Table1,ROW(A1),0)=0,"",VLOOKUP($C$2,Table1,ROW(A1),0)),"")

Open in new window


No named ranges needed really.

HTH

Zack
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

kimhanAuthor Commented:
Thank you very much, your solution worked!  But I noticed a couple of things I am wondering if you could help me out with....

1.  Please note the "Date Will Signed" Area - on Sheet 1 - the date is reading as 20508, rather than February 23, 1956 as indicated on Sheet 2.

2. Is it possible to set up - "Date Will Signed Out" - to read as N/A?

3.  I will have a couple of thousand Wills to log --- and we will have numerous "Smiths" etc....if I type the last name "Smith" ---- how could I set this up so that I could type --- Smith, (first name) to bring up the Smith I need?

4. Lastly, how can I password protect this once I am completed??

I truly appreciate your help!
Copy-of-KJF-WILL-LOG.xlsx
0
Zack BarresseCEOCommented:
No problem. Let me address your list.

1). This is just a format issue. That's still a date. Just select the cell and on the HOME tab look for the Number group. Click the drop down (should say 'General') and choose Short Date (whatever format you want, really).

2) Not really sure what you mean. If you just want an "#N/A" in the cell if there wasn't anything found, just replace the last "" in the formula with NA().

3) Ah ha, this is what I was afraid of. Next time please be very forthright with your end requirements. Makes suggesting solutions a little easier. ;) So you won't be able to search just by last name, that won't work. You need to think about what is unique. If it will be last name and first name, cool, you can do that. What of the possibility of two people having the same first and last name? Will you ever have two John Smith's?

Assuming for a moment that the first/last name combination will always be unique, and also that you are using the table as described above, the following steps will get you adjusted...

Add a column to your table (first blank column is G, that's what I'll use
In G1 enter "FULLNAME" (or whatever you want)
Enter this formula: =[@[LAST NAME]]&", "&[@[FIRST NAME]]
The formula on Sheet1 simply becomes =INDEX(Table1,MATCH($C$2,Table1[FULLNAME],0),ROW(A1))

Now the formula I gave in my last post was with some error handling.  If you want #N/A showing if no value is found, this should give it to you.


4) All cells have their default format of Locked set to true. Those cells you want people to change, set those Locked properties to False. Basically, select them, press CTRL + 1, click Protection tab and uncheck Locked, click OK. Then just right-click the sheet tab and click Protect Sheet. Conversely you can click the buttons on the Review tab as well. :)

HTH

Zack
0
kimhanAuthor Commented:
Hi Zack,

Thank you again.

I've attached the file again for you to take a look at.  

For some reason when I tried making the suggestions you advised above, I'm now not getting any results showing up in the First Name, Date Will Signed, Witnesses, Date Will Signed Out and Lawyer fields again.

I also made the 7th column on Sheet 2 to incorporate the clients full name for searing purposes and used the formula you provided:

=[@[LAST NAME]]&", "&[@[FIRST NAME]]

When I entered it I got an error which I have also attached for you to see.

Thank you again for all your help so far!
Doc1.pdf
KJF-WILL-LOG.xlsx
0
kimhanAuthor Commented:
Hi Zack,

I've been working on this again and I made some headway.  

With regard to the results not showing up in the First Name, Date Will Signed, Witnesses, Date Will Signed Out and Lawyer fields I discovered for some reason on the formulas in all those fields a #REF! somehow appeared after the Sheet2!.  So I re-entered the formulas you provided me on Sept. 18/13.  So that problem seems to be fixed.

When I formatted the dates in the columns on Sheet 2 as you described - I have them set to show up as ie) September 21, 2013 - but they are still showing up as 102113...

Also, as I mentioned above - the Full Name column is still getting that error when I try to enter the formula you provided.

I've attached the file again for you to take a look at.

Thanks again!

Kim
KJF-WILL-LOG.xlsx
0
Zack BarresseCEOCommented:
File attached. The formula was meant to be one with structured table references. In the attached file I inserted a table onto your data and adjusted the formula to concatenate the last and first name. This will be fine so long as you have unique names in the column. If you ever get more than one person with the same first and last name, the formulas will only return the first value found.

The formula on Sheet1 I simply put as ...

=IFERROR(INDEX(tblData,MATCH($C$2,tblData[FULL NAME],0),ROW(A1)),"Not found")

Open in new window

Which will show "Not found" if the name entered doesn't match what is in the data table. I also formatted the date cells with a date format.

HTH

Zack
KJF-WILL-LOG.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kimhanAuthor Commented:
Hi Zack,

Sorry I was away on holidays for a bit and just got back to working on this log.  Thank you so much for all your help...especially for working on the log yourself.  It works great!  I really appreciate it!

Kim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.