VLOOKUP .. help please

[Tim C]

I have been using the VLOOKUP Command to have a cell lookup my parts database. Thing is the database is too large a file so I want to split it up by first letter of part reference. I now have this problem:
=VLOOKUP(C13,PARTS,2,FALSE) .. I need the name PARTS to be a variable dependant on the part number I enter, the VLOOKUP should search the proper database.. things I have tried are:
* =VLOOKUP(C13,CELL("contents",C13),2,FALSE)
the CELL command refers to the cell where the part number is located. In the Formula Auditing, I traced it and it looks like it will work but .. doesn't
* =VLOOKUP(C13,W13,2,FALSE)
where W13 is the cell containing the name of database I want VLOOKUP to search.. that didn't work either.. Does anyone have any other suggestions? I have started looking in making a function in visual basic but am not very familiar with this stuff... Is there just something that I'm not seeing?? Thanks for any help that anyone may provide.

Tim C.

 

[Andrew Poulsom]

Try:

=VLOOKUP(C13,INDIRECT(W13),2,FALSE)

 

[dvc214]

Have you tried using a chain of If formulae?

IF(LEFT(A2,1)="A",VLOOKUP(A2,tableofAs,2,false),IF(LEFT(A2,1)="B",VLOOKUP(A2,tableofBs,2,false),IF ... etc.)

Then you have 26 part tables each beginning with a different letter of the alphabet.

Dave

 

[Tim C]

Thank you so much.. it works like a charm!!

 

[Tim C]

Ok now I have a new problem... With
=VLOOKUP(B3,INDIRECT(M11),4,FALSE) for example, I now need the database open for VLOOKUP to find the part number I need.. where as if I just typed in the actual name of the database in the VLOOKUP, the database file didn't actually need to be open for this to work.. God it's one thing after another!!! HELP please!

 

[Andrew Poulsom]

Yes, the workbook must be open to use INDIRECT as an external reference. I thought your databases were in the same workbook, otherwise I would not have suggested it.

Sorry, I don't know a solution (without VBA), but someone else might.

 

[Tim C]

I know very little about VBA but I'm willing to try if you know a solution.. what commands to use, I can fill in the blanks.. Yeah the idea is that I am putting together a quote for clientel and by typing a part number, I get the description and cost.. My parts database is huge and I wanted to split it up like: APARTS, BPARTS..
but I don't want these files open.. Thanks for your help.. I am so close.. like I said, if you have any suggestions in VBA, I am willing to try..

 

[Tim C]

Dave, your solution works but I also have number 0-9 to deal with, as I was typing in the equation I got a message saying formula too long...