Displaying A Value From A Dropdown Box

[MikeSmith69]

I have put together an archery scorecard and it works really well for my needs, I had help from this forum on a formula I needed to complete it and since then I have used it and am really pleased with it. I would like to take it to the next level so I will try and explain in the best way I can on what I would like to do. Here is a screenshot so I can explain it a little better.

Capture.JPG

As you can see I have a dropdown box that shows all the available rounds that can be shot however every round shoots a different amount of arrows at different distances.

What I would like to do is when I select a round I would like the correct value of how many dozen arrows in D11 and at what distance in I11. I have the values in a worksheet within the document, the dropdown values are being pulled from it. Most of the rounds have a multiple distance so for instance you would shoot 4 dozen at 1 distance and then shoot a further 2 dozen at another distance so if it is possible would need values for the shorter distance to go into the blue bar on row 16 same as row 11.

Any help on this would be greatly appreciated, thank you in advance.

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[JohnTopley]

..probably a VLOOKUP table

Column 1: Round
Column 2: First shooting value (for D11)
Column 3: first shooting Distance (for I11)
Column 4:Second shooting value (d16)
Column 5: Second shooting distance (i16)

Formula:

H4=Round

=VLOOKUP($H$4,$AA$2:$AE$50,2,0) will return value for D11

=VLOOKUP($H$4,$AA$2:$AE$50,3,0) will return value for I11

=VLOOKUP($H$4,$AA$2:$AE$50,4,0) will return value for D16

=VLOOKUP($H$4,$AA$2:$AE$50,5,0) will return value for I16


AA2:AE50 is the lookup table

[MikeSmith69]

..probably a VLOOKUP table

Column 1: Round
Column 2: First shooting value (for D11)
Column 3: first shooting Distance (for I11)
Column 4:Second shooting value (d16)
Column 5: Second shooting distance (i16)

Formula:

H4=Round

=VLOOKUP($H$4,$AA$2:$AE$50,2,0) will return value for D11

=VLOOKUP($H$4,$AA$2:$AE$50,3,0) will return value for I11

=VLOOKUP($H$4,$AA$2:$AE$50,4,0) will return value for D16

=VLOOKUP($H$4,$AA$2:$AE$50,5,0) will return value for I16


AA2:AE50 is the lookup table

Incredible, that is exactly what I wanted, John you are a star mate, thank you so much for taking the time to help me in this matter, very much appreciated

[MikeSmith69]

Out of curiosity if the data for round/arrows shot/distance was stored on another worksheet called data for argument sake what extra info would need to go in the vlookup ??

[JohnTopley]

You are very welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[JohnTopley]

for data on separate sheet ...


=VLOOKUP($H$4,Data!$AA$2:$AE$50,2,0)

[MikeSmith69]

Once again thank you John you are a star

Will mark as solved now

[MikeSmith69]

Scrap last post, sussed it