Excel Search/Lookup

[RussOSU]

This may or may not be possible I am hoping the prior. I am trying to create a search/lookup "application" in an excel workbook. Here is the background:

I have a workbook which has multiple worksheets in it. Each one is named after the model in which the data refers to. The data is not unique to each model so just a straight workbook lookup is not possible. I am wanting to create a main page to which you type the model and the data code and when you hit the search button it takes you to that data code. I should tell you that within each worksheet the data codes are unique. There is only one instance of each code on each sheet however the code could appear in another worksheets. I have many other wants but I am just going with the basic function first before I investigate and rack my brain on them (baby steps right? ) Is it possible to set up this type of search where I translate a cell input into a worksheet name comparison (A1 = Sheet('sheet name')? Second how can I do a lookup each time that takes me to that data code? Place all coding on the button? Can I just use a normal lookup function for this?

Thanks
-Russ

 

[SkipVought]

Russ,

You have made it hard on yourself.

...multiple worksheets in it. Each one is named after the model in which the data refers to.

You would agree that your task would be MUCH EASIER if all the data were on a single sheet, yes?

What you want to do can be done. Fact is, I can tighten a screw with a knifeblade, but a properly sized screwdriver is much better suited for the job. You you could write a complicated VBA routine to do it OR...

put your efforts into restructuring your workbook to make such analysis and reporting easier to accomplish using such built-in tools as Filters, Sorts, Subtotal, PivotTable, Chart, MS Query just to name a few.

Let me know how you choose to proceed.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[RussOSU]

I know the easiest ways would be single sheets or a db however I am trying to simplify the current process (which includes sorting through the worksheets to find the one you want then doing a find to find the dc you want). In the long run I will be trying to redo the process all together however currently I need a bandaid fix to speed this up as the # of sheets and the # of data codes are reaching very large #s.

 

[SkipVought]

OK

Assuming that A1 has the Sheet Name and B1 has the Lookup Value and the corresponding table range in each sheet is columns A:Z, then the lookup formula is
[tt]
=VLOOKUP(B1,INDIRECT(A1&"!A:Z"),2,FALSE)
[/tt]


Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[PHV]

the # of sheets and the # of data codes are reaching very large #s
Are you sure that Excel is well suited ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RussOSU]

Thanks Skip!!

PHV - No Excel isn't the way to go. However I need to have a fix in place before I can go in and design the database application to correct this problem. My ideal fix is going to be creating a server database with a .asp or .php application to allow for data insertion and searching. However I am not a full time programmer so I am consistently rusty with the languages and it takes twice as long to create the apps.

Thanks for all your help!

-Russ