View Single Post
 
Old 11-11-2013, 07:26 AM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote