My first problem is how to explain the problem! in some ways its very
simple, in others its not...I'm afraid this will be somewhat of an
essay!

basically I have a two sheet part creation form work book, the first
sheet is the main sheet which uses a mixture of drop down boxes and
manual entry text cells to achieve the goal of the form. The second
sheet contains all the data for the drop down lists and also contains
most of the formulae, which are mainly nested IF's and nested IF's with
OR functions.

My problem was that IF statments can only be nested to 7, but I needed
40, which I got round on my first workbook by using the OR function
with the first 7 nested IF's allowing me to then split the formulae
into another 6 cells circumventing, for lack of a better description,
Microsofts short sightedness with the number 7.

However I've hit another problem. I now need 86 or more IF statements
and my above method only easily goes upto 49. I've been told I could
use the VLOOKUP function, however for this to work it seems to need a
set table of characters. I do not have this per se as I use a linked
cell from the drop down box as the counter for my forumlae. So for
example rather than having Cell A1 = 1, Cell A2 = 2 and so on, I have
Cell A1 = 1 or 2 or 3 and so on depending which option is chosen from
its linked drop down box. (hope all this is making sense so far). How
would a VLOOKUP function work with this?

basically I have Cells A4 to A89 with the names of parts to be entered
on the spreadsheet. Cells B4 to B89 are the part numbers for these part
names. Cell C4 is the linked cell from the first sheets drop down box
which uses cells A4 to A89 as its selection data.

for example:

Description Part Number Drop down box Linked
Cell Counter
A4 Rokut Rivet B4 P200 C4
can be 1-12
A5 Anchor Rivet B5 P201
A6 Plasti Rivet B6 P202
A7 Micro Push Rivet B7 P204
A8 Push Rivet B8 P206
A9 Two Stage Push Rivet B9 P206
A10 Screw Type Push Rivet B10 P208
A11 R-Lok Expansion Rivet B11 P210
A12 R-Tite Rivet B12 P211

How do I use the VLOOKUP function on the above?

Hope it all made sense! Any help appreciated.

Martin


--
MartinC
------------------------------------------------------------------------
MartinC's Profile: http://www.excelforum.com/member.php...o&userid=30391
View this thread: http://www.excelforum.com/showthread...hreadid=500550

I hope I understand this correctly
You select a name from a dropdown (say dropdown is in C4)
in D4 and you need another cell ( D4) to look up what has been selected,
find it in an array (A1:B89) then retrieve the data in the 2nd column(B)?
then try this
=VLOOKUP(C4,A1:B86,2,FALSE)



"MartinC" wrote:


My first problem is how to explain the problem! in some ways its very
simple, in others its not...I'm afraid this will be somewhat of an
essay!

basically I have a two sheet part creation form work book, the first
sheet is the main sheet which uses a mixture of drop down boxes and
manual entry text cells to achieve the goal of the form. The second
sheet contains all the data for the drop down lists and also contains
most of the formulae, which are mainly nested IF's and nested IF's with
OR functions.

My problem was that IF statments can only be nested to 7, but I needed
40, which I got round on my first workbook by using the OR function
with the first 7 nested IF's allowing me to then split the formulae
into another 6 cells circumventing, for lack of a better description,
Microsofts short sightedness with the number 7.

However I've hit another problem. I now need 86 or more IF statements
and my above method only easily goes upto 49. I've been told I could
use the VLOOKUP function, however for this to work it seems to need a
set table of characters. I do not have this per se as I use a linked
cell from the drop down box as the counter for my forumlae. So for
example rather than having Cell A1 = 1, Cell A2 = 2 and so on, I have
Cell A1 = 1 or 2 or 3 and so on depending which option is chosen from
its linked drop down box. (hope all this is making sense so far). How
would a VLOOKUP function work with this?

basically I have Cells A4 to A89 with the names of parts to be entered
on the spreadsheet. Cells B4 to B89 are the part numbers for these part
names. Cell C4 is the linked cell from the first sheets drop down box
which uses cells A4 to A89 as its selection data.

for example:

Description Part Number Drop down box Linked
Cell Counter
A4 Rokut Rivet B4 P200 C4
can be 1-12
A5 Anchor Rivet B5 P201
A6 Plasti Rivet B6 P202
A7 Micro Push Rivet B7 P204
A8 Push Rivet B8 P206
A9 Two Stage Push Rivet B9 P206
A10 Screw Type Push Rivet B10 P208
A11 R-Lok Expansion Rivet B11 P210
A12 R-Tite Rivet B12 P211

How do I use the VLOOKUP function on the above?

Hope it all made sense! Any help appreciated.

Martin


--
MartinC
------------------------------------------------------------------------
MartinC's Profile: http://www.excelforum.com/member.php...o&userid=30391
View this thread: http://www.excelforum.com/showthread...hreadid=500550