Transfer sheet data from one sheet to another when cell value is changed?

[Alain_G]

Hello all!

I'm not sure how to really achieve this with Excel as I have no coding background and this type of work was unfortunately dumped on me. So I have the following Products in Stock sheet:

So when I enter in Y or N in the Needed? cells, I would like for some type of formula or maybe code to import the serial numbers from the following sheet:

In the event if nothing is entered, I'd like to have the Serial Number cell be blank.

Thank you for your assistance!

 

[Joe4]

Welcome to the Board!

You can do this pretty easily with an IF and VLOOKUP formula.
Place this formula in cell E2 and copy down for all rows:

=IF(OR(D2="Y",D2="N"),VLOOKUP('Products in Stock'!A2,'Serial Number'!A:B,2,0),"")

Here is a more detailed explanation of how to use VLOOKUP between two sheets: Excel formula: VLOOKUP from another sheet | Exceljet

 

[Alain_G]

Welcome to the Board!

You can do this pretty easily with an IF and VLOOKUP formula.
Place this formula in cell E2 and copy down for all rows:

=IF(OR(D2="Y",D2="N"),VLOOKUP('Products in Stock'!A2,'Serial Number'!A:B,2,0),"")

Here is a more detailed explanation of how to use VLOOKUP between two sheets: Excel formula: VLOOKUP from another sheet | Exceljet

Do I need to insert "$" anywhere or can I just copy it down?

 

[Joe4]

Do I need to insert "$" anywhere or can I just copy it down?

You shouldn't need to use it in this case, since we are referencing the entire columns A and B on your "Serial Numbers" sheet.
If you wanted to use an exact range instead, then you would use the "$" to lock that range down.

 

[jdellasala]

The formula in Cell E2 of the "Products in Stock" worksheet would be:
=IF(C2<>"Y","-",VLOOKUP(A2,'Serial Numbers'!$A$2:$B$8,2,0))
NOTE: You will have to adjust the formula if you add items to the data in the "Serial Numbers" worksheet.

If you convert that data into an Excel Table, the formula would then be:
=IF(C2<>"Y","-",VLOOKUP(A2,Table1,2,0))
As you add items to the Table in the "Serial Numbers" worksheet, the table_array reference in the 2nd formula would expand automatically.

To make the data in the "Serial Numbers" worksheet into an Excel Table, select any single cell within the data and select Insert -> Table. The range should be selected automatically, and make sure the checkbox next to My Table has headers is checked.

Best practice is to give the Excel Table a descriptive name rather than relying on the default which is in the 2nd example above. Once the Excel Table has been created, with any cell within the Table selected, go to the Table Design ribbon and at the far left change the Table Name in the field from Table1 to something like SerialNumbers. (Spaces are not allowed in a Table Name). The equation would then become:
=IF(C2<>"Y","-",VLOOKUP(A2,SerialNumbers,2,0))

Note that both upper and lower case Y will invoke the VLOOKUP function in Column C. The VLOOKUP will only run when Y or y are entered into a cell in Column C making execution quicker.

There are tons of videos on Excel Tables and the VLOOKUP function on both Mr. Excel's YouTube channel (https://www.youtube.com/channel/UCXbicpVq_ALWG4ijPKsR7ZQ) and the ExcelIsFun channel (https://www.youtube.com/channel/UCkndrGoNpUDV-uia6a9jwVg) which I recommend because they are geared towards education and always have accompanying Excel files. The two channels collaborate often.

 

[jdellasala]

Do I need to insert "$" anywhere or can I just copy it down?

The answer is no. Joe4's VLOOOKUP formula references the entire column (A:B) so does not need to be locked ($). The same can be done with my formula:
=IF(C2<>"Y","-",VLOOKUP(A2,'Serial Numbers'!A:B,2,0))
which would negate having to turn the data into an Excel table.
In my formula I assumed you'd only want the Serial# if you entered a Y. If you want the Serial# if either Y or N is entered in Column C, I'd still recommend doing the false statement first to prevent VLOOKUP from running unless Y or N is entered:
=IF(AND(C2<>"Y",C2<>"N"),"-",VLOOKUP(A2,'Serial Numbers'!A:B,2,0))

 

[Joe4]

The answer is no. Joe4's VLOOOKUP formula references the entire column (A:B) so does not need to be locked ($).

Yep, I already answered that a while ago!

 

[Fluff]

Thread locked as acount has been closed.