Ask Your Question
0

drop down list to fill other cells in row

asked 2019-09-16 04:22:42 +0200

clos911 gravatar image

updated 2019-09-16 04:23:25 +0200

I just added drop down lists on my inventory spreadsheet.

I'm using the drop downs for the 'item type' column (column A), with the source being an item list on a different sheet within the spreadsheet.

column B is for the dimensions, column C for weight and a few more detail columns. is there a way to automatically have the other fields filled in with the details once I select the item type from the list?

thank you

edit retag flag offensive close merge delete

Comments

1

Not quite clear what is required - do you want

  1. drop down lists for column B, column C, ... to be filled depending on the selection in column A --or --
  2. values to be filled into column B, column C, ...depending on the selection in column A (since they are unumbigous if value in column A is defined). The solution to this would be a simple VLOOKUP in what you called a different sheet within the spreadsheet
Opaque gravatar imageOpaque ( 2019-09-16 10:15:46 +0200 )edit

Yes 2, trying vlookup worked exceptionally! very nice!

The only issue I have now is when my drop down list is selected and I insert a row below, the inserted row has the drop down list which is great, but the vlookup formula does not.. I copied the cell with the formula and did a special paste of formula only, doing so the formula's criteria changes to the next cell down but so does the array range, here's the formula, 1) is the correct formula, 2) is what is formula pasted, you can see the array has changed...

  • 1) =VLOOKUP(A2,$'Inventory'.A2:M41,2,0)

    2) =VLOOKUP(A3,$'Inventory'.A3:M42,2,0)

is any way to have the formula array stay the same when pasting or any other way to insert rows below to have the formula added without array change?

Thank you

clos911 gravatar imageclos911 ( 2019-09-16 22:51:57 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2019-09-16 23:15:51 +0200

Opaque gravatar image

updated 2019-09-16 23:17:52 +0200

Hello

regarding your question in your comment above, you need to use absolute references using $ - thus rewrite your VLOOKUP() to:

=VLOOKUP(A2,$'Inventory'.A$2:M$41,2,0) -- or -- if you also copy to other columns =VLOOKUP(A2,$'Inventory'.$A$2:$M$41,2,0)

This way you can copy the formula to any cell, provided that range $A$2:$M$41 covers all of your inventory data.

Hope that helps.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-09-16 04:22:42 +0200

Seen: 44 times

Last updated: Sep 16