macro to search for an variably input field...
Posted by Keith on August 20, 2001 5:11 AM
Hello! I was wondering if anyone has any ideas on how to write a macro where a person could type a variable into a field (ex. "Smith, J") and have the macro return all of the data associated with that variable? I guess what I imagine is having a worksheet within a workbook where non-techies could go and search for data without them having to know how to use filters. I've played with some active x controls, but find that my programming knowledge is woefully lacking... Any help or advice would be greatly appreciated! Thanks! :-)
Posted by Rob Jackson on August 20, 2001 8:19 AM
There are a number of options, some not needing code. Say for example you want to create a simple phonebook. You have the data on a sheet named data and row 1 is blank and column A is blank. Names are in B2 to B10 and Tel# are in C2 to C10. On your inquirey sheet you have a field where you input the name you want, Say F6. Somewhere say F7 you put the formula
= Match($F$6,Data!B:B,0)-1
(The minus 1 is required due to the offset you will use shortly)
In say F8 put the formula
=if(ISNA($F$7) ,0,$F$7)
Then where you want the phone number to appear put the formula:
=offset(Data!$A$1,$F$8,2)
If you have a further column in Data!C such as mobile then the formla in the cell where you want this number to appear is:
=offset(Data!$A$1,$F$8,3)
The hide all relevant bits and protect the workbook if necessary, you could add a DD box to choose the name and use the item number it dumps in the linked cell to run your offsets)
Hope this helps.
Rob
Posted by Rob Jackson on August 20, 2001 8:21 AM
I forgot to mention, you use the blank row/column in case of a failed match. This way you get blanks rather than garbage.
Rob
Posted by Keith on August 20, 2001 8:35 AM
Hello Rob,
Thank you! I'm not currently familiar with "DATA!" and "ISNA", but I'm going into help right now to try to figure it out and I'm sure that this will work great!
Thank you again!
~Keith
Posted by Rob Jackson on August 21, 2001 12:04 AM
if your referring to a different sheet, the construct is
Sheetname!A1
If you get no match then it returns an n/a error. ISNA give you a TRUE or FALSE as to whether you are getting this error.
Rob
Posted by Keith on August 21, 2001 4:33 AM
yeah, I felt like a doof...
Thank you, Rob. Yeah, I felt like a doof after writing about the Data! comment once I realized what it was. I set up the formulae as you prescribed and got great results, but, unfortunately, this only provides for a single entry. Is there a way to allow for multiple entries that meet the search criteria (like two or more entries of "Smith"? Thank you, again, for your help.
~Keith if your referring to a different sheet, the construct is
