Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function for counting Worksheets? | Excel Worksheet Functions | |||
Function to sum same cell in several worksheets | Excel Discussion (Misc queries) | |||
Using SUMIF Function to get data from several worksheets | Excel Worksheet Functions | |||
Function to Return another Worksheets Name | Excel Worksheet Functions | |||
Assigning Cells in worksheets to other data in other worksheets. | Excel Discussion (Misc queries) |