How to populate information from one sheet to another in Excel Workbook?

Question

My current task is to create a formula where my information from one sheet will auto populate in another sheet when the product description is copied.

To be specific, we need to have the items listed as (ACCESSORIES) under the (Part Type) setting on the Parts Download Sheet populate the (ACCESSORIES) section of the Region Information sheet which consists of columns: AQ & BI.

The idea is to copy the description from column (B) from Parts Download Sheet and have it populate the price from column (G) in Parts Download sheet, to Column (BI) in Region Information sheet.

I've added screenshots:

How can this be done? Is this a Vlookup formula or Macros code issue?

COLUMN BI UPDATED AND POPULATED

Solution

From what I can understand, the following VLookup formula, placed in cell BI11 and dragged downwards should gather the data you require:

=VLOOKUP(AQ11,PartsDownload!B2:G65536,6,FALSE)

Changing the row numbers as required.

To explain the arguments of VLOOKUP in simple terms:

=VLOOKUP(LookupCell,SearchRange,ColumnIndex,LazyMatch)

LookupCell is the cell you want to compare to, in your case, the description

SearchRange is the group of cells you want to search for. The first column of this must be the set of rows you want to search for.

ColumnIndex is the index of your search range with the data you want back. In your case, the column was the 6th column of your search range

LazyMatch is a true or false for whether the LookupCell must match 100%. If true, it doesn't, if false, it must be identical.

Hope this helps!

Comments

[Jonno]

Sounds like VLookup is exactly what you're needing, although I can't see any of the screenshots you mention?

[Elon Daly]

Thanks for the response. I've fixed the screenshot links. I've also been trying Vlookup all day to no avail.

[Elon Daly]

I also have the workbook available for download for easy access.

[Jonno]

I'm just trying to work out exactly what you want to do now I can see the screenshots - you want everything that is marked 'ACCESSORIES' to be pulled into your other sheet? Or did you want to put specific descriptions into your Region Information sheet and have it grab the correct values based on that?

[Elon Daly]

The latter is correct. I'd like it to be where you copy the description from Parts Download and the price values from Column G automatically populate when you paste.

[Jonno]

Can you confirm to me what column you'd want to 1) Paste the Description into - and 2) gather the price into on the Region Information sheet?

[Elon Daly]

That would be columns 1) AQ & 2) BI respectively. From cell 12 and onward.

[Jonno]

That's what I thought you said, but AQ looks like it's for 'Unit of Measure', and BI is for 'Units per ordering unit'? Do you mean AP and BH?

[Elon Daly]

Yes, that screenshot was taken a few hours ago. I have added the updated screenshot showing the correct column as AQ. I've been making additions to the sheet until I got this fixed.

[Elon Daly]

Also the descriptions were copied.

[Elon Daly]

Trying that now.

[Elon Daly]

GOT IT!!! I've uploaded the new screenshot. Can you explain what I did wrong? I'll have to do a lot more of this in a few hours.

[Jonno]

Glad it's working - I can see your new screenshot but I'm not sure what part I'm looking at?

[Elon Daly]

I've added the correct screenshot displaying Column BI populated. Thank Jonno, I've been dealing with this for 12 hours. I'm not sure why my old formula didn't work.

[Jonno]

I'm not sure what you did wrong as I can't see what you tried before :) But glad it's working for you now..! I'll update my answer explaining VLOOKUP if that helps you going forward.

[Elon Daly]

It definitely will. And I will be approaching some now areas in a few hours. Thanks again.