Use worksheet as user interface for database

[segehring]

I am trying to use a single worksheet as a user interface for a material database. Basically I want to be able to input our internal material number into Excel and have it pull various parameters from anumber of other sheets within the same workbook.

We use an 11 place number (ex 10435013828) that is broken down into the following

Position(s) Definition #of Worksheets
1 & 2 Material type (ex. Steel) 1
3 Form (ex. Sheet, bar, tubing) 1
4 & 5 Material Specifications 15
6 & 7 Material Tempering 1
8 - 11 Material Size 1

On each worksheet there are as many as 3 columns. I need it to search for the correct value in the first column and return the data from the second and third column of the corresponding row.

For the data that is spread over 15 sheets, there is a designated cell on each that corresponds to one of the material type numbers found in positions 1 & 2. Basically it will have to search for the correct sheet, then search for the correct row.

I am very new to using macros, I assume that they will be necessary, and would appreciate any help I can get.

Thanks in advance

Shane

[figment]

how is this 11 diget number entered? is it all in one cell or is it broken up over multiple cells ? it this code purly for data retreaval or will you want it to alow for data editing? and will the user be able to enter more then one number at a time?

it would be most helpfull if you could post an example of your worksheet.

[segehring]

The number will be entered into one cell. I have it set up to extract the numbers corresponding to each particular position using the MID() command.

This will be for data retrieval and I probably don't need to have the ability to enter multiple numbers. Although, having that capability wouldn't be all bad.

One other thing that I would like to do is have a second sheet where you could enter portions of the data and have it return the correct code number. Basically do the reverse of what the first scenario does.

I have attached a workbook with all of the relevant sheets but with most of the data removed, to simplify things and reduce the size.

Thanks for the help, it is appreciated.

Shane

[Bob Phillips]

Muchof it can be done with lookups

=VLOOKUP(A4,'Material Codes'!$A:$B,2,FALSE)

but not sure about Materials Specifications and Materials Size Codes

[segehring]

That works for everything except the Material Specifications. For that I will have to use a macro. I believe that I can use

Select Case ActiveSheet.Range()

to actually select the worksheet. The problem I am having is that I don't know how to initiate the command or how to go to a command that will basically use the vlookup on the selected sheet. The only way that I know how to do this is to use an if() statement with all of 15 sheets defined in it.

I will try that for now but if anyone can suggest an easier/better way, I would be most grateful.

Thanks

Shane

[Bob Phillips]

I bet it can be done with a formula.

What are the rules?

[segehring]

Basically I have 15 different material types, each has its own worksheet. based on the first two numbers in the ID code, I need to take the number that is found in the fourth and fifth places and search the respective sheet for a match. The data in the second column needs to be returned.

The VLOOKUP command works for all of the others but I can't get it to work for this one. I keep getting #N/A with no real explanation as to why. I haven't even tried using an IF() statement yet b/c I can't get the other to work. Any ideas? I posted a sample of the workbook earlier.

Thanks

Shane

[Bob Phillips]

Create a table in say T2:U16 of

10 10 SST
11 11 SST
15 15 Steel
16 16 Steel
20 20 Copper
22 22 Brass
24 24 Bronze
30 30 Aluminum
31 31 Aluminum
40 40 Rubber
50 50 Thermoplastics
51 51 Thermosets
70 70 Solder & Brazing
90 90 Miscellaneous
91 91 Miscellaneous

and use

=VLOOKUP(E4,INDIRECT("'"&VLOOKUP(--D4,T2:U16,2,FALSE)&"'!A:B"),2,FALSE)

[segehring]

Can you explain this a bit more? I'm not sure what this is for. The VLOOKUP works for all of the worksheets except the Material Specification worksheets. (10 SST, 11 SST, 15 Steel, 16 Steel, 20 Copper, 22 Brass, 24 Bronze, 30 Aluminum, 31 Aluminum, etc.)

I reposted with the formulas already in place. They can be found in Column J, Rows 4-8 on the Data Entry worksheet.

Until I can get the VLOOKUP command to work with the mat. spec. sheets, I am leaving out the if() statement.

Thanks

Shane

[figment]

try this

Attachment 7504

the code creats the needed vlookup functions. it relyes on the named ranges that you had already set up in the example sheet.

[Bob Phillips]

Can you explain this a bit more? I'm not sure what this is for. The VLOOKUP works for all of the worksheets except the Material Specification worksheets. (10 SST, 11 SST, 15 Steel, 16 Steel, 20 Copper, 22 Brass, 24 Bronze, 30 Aluminum, 31 Aluminum, etc.)

I reposted with the formulas already in place. They can be found in Column J, Rows 4-8 on the Data Entry worksheet.

Until I can get the VLOOKUP command to work with the mat. spec. sheets, I am leaving out the if() statement.

Thanks

Shane

Put the lookup tabe that I gave you in T2:U16, and then use

=VLOOKUP(--D4,INDIRECT("'"&VLOOKUP(--A4,T2:U16,2,FALSE)&"'!A:$B"),2,FALSE)

[segehring]

Thanks for the help with the macros and setting this up for multiple returns. I am still getting #N/A in the mat. spec. column. Any ideas? Could this be because of formatting?

I am thoroughly confused as to why it works for all the other worksheets.

Shane

[segehring]

Put the lookup tabe that I gave you in T2:U16, and then use

=VLOOKUP(--D4,INDIRECT("'"&VLOOKUP(--A4,T2:U16,2,FALSE)&"'!A:$B"),2,FALSE)

Which worksheet? Data Entry?

Thanks

[Bob Phillips]

Yeah, that will do nicely.

[segehring]

xld,

Have you gotten this to work, because it doesn't work for me. If you did get it to work, can you post it?

Thanks

Shane

[figment]

the problem appers to be with your numbers in column A. i when through the 10 SST sheet and retyped all the numbers in column A and now the vlookups that that my macro generates are working. unfortunetly i am not sure what is wrong with your origenal numbers.

[segehring]

the problem appers to be with your numbers in column A. i when through the 10 SST sheet and retyped all the numbers in column A and now the vlookups that that my macro generates are working. unfortunetly i am not sure what is wrong with your origenal numbers.

I agree that there was something wrong with the numbers but it still wouldn't work for me. I put in just the code for the vlookup w/o the mid() command and it worked. As soon as I put the mid() command back in, it quit working. Any suggestions?

Thanks

Shane

[figment]

well if the mid dosn't work the easy answer is to remove the mid. give this a try

Attachment 7506

[Bob Phillips]

xld,

Have you gotten this to work, because it doesn't work for me. If you did get it to work, can you post it?

Thanks

Shane

Of course I did. I can't buy it here because for some reason I don't get the advanced button, so I posted it here http://cjoint.com/?mnxShnkopr