0

The doc I am using is linked below. I am trying to set it up to copy information from the Sheet titled "Rates" to the Sheet titled "PO" based on info that is selected in the dropdown menu in cells C/D 19-29 on "PO".

https://docs.google.com/spreadsheets/d/1rzlEbp9fEwRwqtfGHrgLbtYqRfFM1FEtpzvugk6Wdok/edit?usp=sharing

I have created a dropdown list in columns C/D under "ITEM". The items in the list match the items in column B on the Sheet "Rates". I would like to set it up to copy the data from Columns A and C on "Rates" and populate it in columns B and E, respectively on "PO". So, for example, if I select "Bloody Mary Box - Front Label" in the dropdown in cell C/D 19 on "PO" (which corresponds with cell B2 on "Rates) I would like it to automatically populate the data from corresponding cells A2 and C2 on "Rates" into cells B19 and E19, respectively on "PO".

Can anyone help me create a formula to do that? Of course, I am happy to clarify if needed.

2

Place this in cell E19

=ARRAYFORMULA(if(ISBLANK(C19:C29),, VLOOKUP(C19:C29,Rates!B2:C24,2,False)))

It will look up the values from the Rates sheet and apply them, and do this for rows 19-29. If there is no value in column C it will return no value.

The Arrayformula and use of array ranges, C19:C29 applies this to every row in that range. The IF test the cell in column C. If it is blank, the test returns TRUE. Since we have ,, taking us past the IS TRUE portion of the IF statement, we return no value when cell C is empty. When cell C has a value, we look that up in the Rates sheet via VLOOKUP() and return the second column of the range we search. The FALSE for VLOOKUP means the data does not have to be sorted so it looks for an exact match as opposed to the first closest match.

  • Hi Karl, Thank you very much for your help with this. That worked perfectly for auto-filling the correct information in column E of the PO sheet. How would I adapt that formula for cell B19 so that it populates the associated data from column A2:A24 on the Rate sheet? My attempts up to this point have not been successful. Thank you so much; this is tremendously helpful! – Justin Jan 24 '17 at 23:26
  • @Justin use the formula as is, but changing any range of C19:C29 to A2:A24 should work. – Karl_S Jan 25 '17 at 2:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.