hello everybody. I have created tables each one with its own name. These tables have fixed values: Height, Width, Length. For each table I have created a sister table. This because the operator can choose whether to use Inch or Cm. Now I need to create an easy system for the operator to input the desired data and get a quick result. What I was thinking about was a sheet where the operator can choose the name of the table, inch or cm, Height - Width - Length (if all 3 sizes are available this could result in a check to see if the package will fit). By doing this "search sheet" I can then hide the tables, this to avoid errors.



Can this be done?

thank you all for any input

There's a lot you didn't say, s7y, and it could be I haven't correctly understood what you want to do. Here's what I heard: You want the user to be able to enter some value, a measure of length, and have Excel do a VLOOKUP on that and return some other value, a price or something. But you want the user to be able to specify ahead of time whether the value he's entering is a height, width or length, and also whether it's in inches or centimeters. Am I right so far? And you have in mind accomplishing this by creating six different tables and requiring the user to supply two additional values that will determine which of the six tables should be used in the VLOOKUP.

I can think of at least one way it can be done, and I'm sure there are others. The first way I thought of puts the six tables each on separate worksheet (and hides those six worksheets), then uses the INDIRECT function to do a VLOOKUP on the correct worksheet. If the tables are large, or if there are a lot of lookups to be done, that may be a little slow; I've a notion putting the six tables on a single worksheet and using OFFSET might be faster. And it's just possible that a VBA solution would be better, though off-hand I don't see that.

Or maybe you don't want the user to enter the value; maybe you just want him to specify height/width/length and inches/centimeters and have the correct table displayed in front of him? That should be just as easy, and easier for the user unless the tables are extremely large. I don't think I'm picturing the situation quite right, though.

Hi Bob, thanks for the reply. The tables I use are the max sizes loadable thru the doors of a 747F. On this airplane we have 5 diffent doors, and therefor I have created 5 different tables.

"But you want the user to be able to specify ahead of time whether the value he's entering is a height, width or length, and also whether it's in inches or centimeters. Am I right so far? And you have in mind accomplishing this by creating six different tables and requiring the user to supply two additional values that will determine which of the six tables should be used in the VLOOKUP"

This is exactly what I am trying to achieve. I don't need the operator to see the tables. I only need the result. I.e. if I have a piece H 114 W 20 then L = 458. the user should only see 458

Ok, it's as I suspected and I misunderstood at first. I was thinking you had six tables, but you have ten, two for each door.

Now, your workbook has to determine which of the ten tables to use. One choice is easy; the user must somehow indicate whether his figures are in inches or centimeters. But the other I'm not sure of. He must give two figures, I gather, and indicate whether they are height, width or length; and your logic must take those two figures, figure out which door(s) they'll fit through, and then figure out the maximum quantity of the missing dimension that will fit through one of those doors. For example: The user has a package that is 1800cm high and 3000cm long. Three of the doors won't take that package at all; but there is one door that can take it if it's no wider than 400 cm, and another than can take it even if it's as much as 1100 cm. So the user enters "cm", 1800, "height", "3000" and "length", and the worksheet gives 1100 as the answer. Like that?

But if so, I'm having trouble picturing how it is that you can have just one table for each door (or rather two, depending on in/cm). Could you let me have a look at the tables for two of the doors, just so I can see how this works?