IF function using two worksheets and various data
 

I have one worksheet that has stock numbers in one row and account numbers in
one column. On my other worksheet, I have account and stock and prices in
separate columns. I want to populate my first worksheet with the prices that
are on my second worksheet without having to retype in all the data. It is
over 200 different stocks and will take a lot of time. Thank you for any
help.

IF function using two worksheets and various data
 

You should be able to do this with the SUMPRODUCT() function.

On the sheet that needs to be filled in with prices, I assume your Account
numbers are in column A, beginning at row 2, while your stock numbers are in
row 1 starting in column B.

On the other sheet I assume everything starts on row 2, with labels in row 1
and column A with Account numbers, column B with stock numbers and column C
with prices.

In cell B2 on the first sheet, enter the following formula (changing the
reference to row 9 to the last row number used on the second sheet, and the
sheet name) and fill it down and to the right in your table:
=SUMPRODUCT(--(Sheet2!$A$2:$A$9=$A2),--(Sheet2!$B$2:$B$9=B$1),--(Sheet2!$C$2:$C$9))

Note: that is all on one line in your cell, not on 2 lines as this forum may
break it into.

Hope this helps.


"nidabland" wrote:

I have one worksheet that has stock numbers in one row and account numbers in
one column. On my other worksheet, I have account and stock and prices in
separate columns. I want to populate my first worksheet with the prices that
are on my second worksheet without having to retype in all the data. It is
over 200 different stocks and will take a lot of time. Thank you for any
help.

IF function using two worksheets and various data
 

Actually, on the empty sheet, my account numbers are in column A, row 4 and
my stock numbers start on row 3, column D. I tried this formula but it
didn't populate the way I wanted. I need the prices on sheet 2 to just
transfer to the right cell in sheet 1. The difference between the two sheets
is that the first (empty) sheet is all my numbers separated by stock while
the second is just in columns of data.
Thanks anyway.
"JLatham" wrote:

You should be able to do this with the SUMPRODUCT() function.

On the sheet that needs to be filled in with prices, I assume your Account
numbers are in column A, beginning at row 2, while your stock numbers are in
row 1 starting in column B.

On the other sheet I assume everything starts on row 2, with labels in row 1
and column A with Account numbers, column B with stock numbers and column C
with prices.

In cell B2 on the first sheet, enter the following formula (changing the
reference to row 9 to the last row number used on the second sheet, and the
sheet name) and fill it down and to the right in your table:
=SUMPRODUCT(--(Sheet2!$A$2:$A$9=$A2),--(Sheet2!$B$2:$B$9=B$1),--(Sheet2!$C$2:$C$9))

Note: that is all on one line in your cell, not on 2 lines as this forum may
break it into.

Hope this helps.


"nidabland" wrote:

I have one worksheet that has stock numbers in one row and account numbers in
one column. On my other worksheet, I have account and stock and prices in
separate columns. I want to populate my first worksheet with the prices that
are on my second worksheet without having to retype in all the data. It is
over 200 different stocks and will take a lot of time. Thank you for any
help.