Auto fill from pick list
 

I have a sheet with different "box" sizes. input via a drop down list.
Dimension a Dimension b
1100 1200
600 800
800 1000

Then a pick list which gives either SHORT side or LONG side leading.

The user picks one off the 1st list e.g. 1100 1200
If they pick SHORT the form should fill in two boxes
Width 1200
Length 1100
But if the user picks LONG, the auto fill should swap the two dimensions
Width 1100
Length 1200

Auto fill from pick list
 

Try this.

Assume A1 holds a drop down for:

Dimension a Dimension b
1100 1200
600 800
800 1000

B1 holds a drop down for: Short, Long.

Width:

=IF(OR(A1="",B1=""),"",IF(B1="Short",--MID(A1,FIND("
",A1)+1,100),--LEFT(A1,FIND(" ",A1)-1)))

Length:

=IF(OR(A1="",B1=""),"",IF(B1="Short",--LEFT(A1,FIND("
",A1)-1),--MID(A1,FIND(" ",A1)+1,100)))

--
Biff
Microsoft Excel MVP


"AndyB" wrote in message
...
I have a sheet with different "box" sizes. input via a drop down list.
Dimension a Dimension b
1100 1200
600 800
800 1000

Then a pick list which gives either SHORT side or LONG side leading.

The user picks one off the 1st list e.g. 1100 1200
If they pick SHORT the form should fill in two boxes
Width 1200
Length 1100
But if the user picks LONG, the auto fill should swap the two dimensions
Width 1100
Length 1200

Auto fill from pick list
 

Hi Biff, not quite, let me explain better.

Cell E23 is a drop down list which uses a VLOOKUP to fill in H23 & J23
e.g. E23=CHEP then H23=1200, J23=1000
E23=Euro Then H23=1000, J23=800

Cell F26 is another drop down with either Short or Long as the options.
This should auto fill G33 & G34 with whatever is in H23 & J23

If E23=CHEP (user pick)
H23=1200 (auto)
J23=1000 (auto)

If F26=Short then G33=1200 G34=1000
But if F26=Long then G33=1000 G34=1200

Andy

Auto fill from pick list
 

Ok...

I'm assuming F26 has only 2 selections available: Short or Long.

G33:

=IF(OR(E23="",F26=""),"",IF(F26="Short",H23,J23))

G34:

=IF(OR(E23="",F26=""),"",IF(F26="Short",J23,H23))

--
Biff
Microsoft Excel MVP


"AndyB" wrote in message
...
Hi Biff, not quite, let me explain better.

Cell E23 is a drop down list which uses a VLOOKUP to fill in H23 & J23
e.g. E23=CHEP then H23=1200, J23=1000
E23=Euro Then H23=1000, J23=800

Cell F26 is another drop down with either Short or Long as the options.
This should auto fill G33 & G34 with whatever is in H23 & J23

If E23=CHEP (user pick)
H23=1200 (auto)
J23=1000 (auto)

If F26=Short then G33=1200 G34=1000
But if F26=Long then G33=1000 G34=1200

Andy