[Solved] Specific Question Regarding Auto Populating between Worksheets

[bkelley@ensembleartsphill]

Hi, Love your site! I hope you can help me come up with a solution. I am trying to create an easier way to complete a report on event prices. I would like to find a way to have the costs/price column auto populate the correct amount based on the venue entered in the previous column, but I have the prices for each venue on a different sheet.

Here's an example: I am working on sheet titled Events. Column F has the names of the venue for each event. Column G would be where the price goes. All prices for each venue are listed in another sheet title Venue Costs. How can I get column G in the Events sheet to populate the appropriate prices from the Venue Costs sheet but pull the correct price based on the venue listed in Column F?

Is that even possible?

 

[shamimarita]

Hello Brea Kelley,

Yes, you can auto-populate venue prices in Column G of the Events sheet based on the venue listed in Column F by using the VLOOKUP function.

Use the following formula:
=IFERROR(VLOOKUP(F2, 'Venue Costs'!$A$1:$B$6, 2, FALSE), "Price not found")


Download Excel File:

 

[bkelley@ensembleartsphill]

Thank you! I think I figured it out. Now I do have over 100 rows that need prices autopopulated. Is there a way to copy the formula for all of those entries without having to edit the table_array ?

 

[bkelley@ensembleartsphill]

Also how should I edit the formula if there are two venues entered in Column F and I need the prices for both venues to be added together and give me a total for that row?

 

[shamimarita]

Thank you! I think I figured it out. Now I do have over 100 rows that need prices autopopulated. Is there a way to copy the formula for all of those entries without having to edit the table_array ?

You're most welcome! To apply the formula to multiple rows, simply drag the fill handle (the small square at the bottom right corner of the selected cell) down to cover all rows. Also, you can use the dynamic cell reference Venue Costs'!A:B.

 

[shamimarita]

Also how should I edit the formula if there are two venues entered in Column F and I need the prices for both venues to be added together and give me a total for that row?

If there are two venues entered in Column F, you can use a combination of SUM and VLOOKUP. Assuming venues are separated by a comma (,)
Formula:
=IFERROR(IF(ISNUMBER(FIND(",", F2)),VLOOKUP(TRIM(LEFT(F2, FIND(",", F2)-1)), 'Venue Costs'!A:B, 2, FALSE) + VLOOKUP(TRIM(MID(F2, FIND(",", F2)+1, LEN(F2))), 'Venue Costs'!A:B, 2, FALSE),VLOOKUP(F2, 'Venue Costs'!A:B, 2, FALSE)),"Price not found")