reword last post RE: move data to sheet 2

[SAMJ]

Thanks so much for the replies!!! I have to admit I am WAY over my head here! I believe what I am trying to accomplish is to link information in cells B1 and C1 to A1 on sheet 1 so that when the information located on sheet 1 A1 is entered into an area on sheet 2, cells B1 and C1 will automatically populate. I have tried the VLOOKUP but that is not working for me. Any other ideas?? All your help is very greatly appreciated!!! Thanks Sam
ray:

 

[Ron Morris]

Hi,

To populate the data onto sheet2 in B1 & C1 based on what is in A1 of both sheet1 and sheet2. Do the following:

Say on sheet2 you are entering the value of sheet1 (A1) in cell A1 (Sheet2).

Enter this formula in cell B1 of sheet2.

=VLOOKUP(A1,Sheet1!A:B,2,false)

Enter this formula in C1 in Sheet2.

=VLOOKUP(A1,Sheet1!A:C,3,false)

In the A:C of the formula, the A represents column 1 and the :C will mean columns A:C.

The 2 in the first formula represents the 2nd column and the 3 in the 2nd formula represents the 3rd column. If the value is not found, #N/A will be the returned value.

If referencing a number, you have to make sure they are formated the same or #N/A will return.

Ron

 

[SAMJ]

WOW! Ron you are so cool. But that is not quite what I need. I need to be able to enter info into cell A1 on sheet 2 that would be found in the A column on sheet 1. At that point cells B1 and C1 on sheet 2 would automatically populate from info in the same line as the information on sheet 1. I know I have to link all the information for the row together but not sure how to do that either. Your help is greatly appreciated!!! Thanks so much!! Sam

 

[Ron Morris]

Hi Sam,

It would probably be best if you can post a small sample of your data so we can see exactly what you are wanting to do.

Does the data you want to populate from sheet1 to sheet2 always resde right below the refernece in column A?

Ron

 

[SAMJ]

Hey Ron!! Thanks so much for the suggestion, I will try it this afternoon. In the meantime per your suggestion here is a small sampling of what I am trying to do ( should have thought of this to begin with - sorry!!) On sheet one I have about 120 rows of infor like the following:

SHEET 1

STOCK# COLOR MODEL COST
1 RED FOCUS 17432.51
2 WHITE EXPLORER 29876.89
3 BLACK ESCAPE 21109.45

On sheet 2 I need to be able to enter a stock number such as "1" and have "RED" , "FOCUS" , AND "17432.51" automatically pop into the coresponding cells.

SHEET 2
STOCK# COLOR MODEL COST


I know there has to be a way to do it but I am really having a hard time with this. Your help and expertise is very greatly appreciated!!! Thanks, Sam

Also the information on sheet 1 will be changing as inventory is updated. rows will be added and removed as necessary. So, whatever stock number is entered onto sheet 1 col. A needs to be linked to the corresponding cells B, C, and D.

 

[SAMJ]

Also, the information will be changing. So, whatever stock number is entered into sheet 1 column A needs to be linked to the coresponding B, C, and D cells. Thanks

 

[Ron Morris]

Hi Sam,

Based on the example of data you provided, the VLOOKUP formula should do the trick.

on sheet 2, in cell B2 enter:

=VLOOKUP(A2,Sheet1!A:B,2,FALSE)

on sheet 2, in cell C2 enter:

=VLOOKUP(A2,Sheet1!A:C,3,FALSE)

on sheet 2, in cell D2 enter:

=VLOOKUP(A2,Sheet1!A:D,4,FALSE)

If this does not work, please type in exactly how you are typing in your formula.

Ron

 

[SAMJ]

Hey Ron. The formula you gave works to populate info into cells B,C,and D on page 2 but does not have anything to do with column A. I need to be able to enter any cell from column A and have the corresponding cells fall in. From all of the information listed on sheet 1, only 1 row will be used on sheet 2. So that when you enter the stock number on sheet 2 (whichever row it is in on sheet 1) the rest of the info will fall into place. Thanks, SAM

 

[Ron Morris]

Hi Sam,

If I understood correctly, if you enter, let's say 1, in A2 of sheet2, the formula will find the associated data from Sheet1 based on the contents of A2 of Sheet2, and populate into the columns of Sheet2. You should be able to enter 99 into A2 of sheet2 and the data from Sheet1 will fill in to columns B, C, D.

If you are in fact dealing with numbers, make sure the both A columns in the sheets are formatted the same way or the formula will return #N/A.

Ron