Home |
Search |
Today's Posts |
#1
|
|||
|
|||
data structure and my problem
Hi
thanks for the reply. I tried including the entire table, but the error now is N/A for all the entries.. the problem is.. i have a small list of products and I want to know their sales price(the last time they were sold). I have a big excel sheet with historic sales data in seperate worksheet. Now, I want to use vlookup to find the sales price of the list of products. the sales proce is in worksheet'historic_sales' in coloumn 'P'. The small list of products whose prices i need are in worksheet 'list'. Hope i made my problem clear. thanks a lot for a help cheers, samantha |
#2
|
|||
|
|||
For demo purposes, I'll assume the following:
1)Sales are entered on the Historic Sales sheet in chronological order. 2)Products are listed in col O on that sheet Enter a product in cell A1 on Sheet 1. Enter this formula in cell B1: =INDEX('Historic Sales'!$P$1:$P$1000,MAX(IF('Historic Sales'!$O$1:$O$1000=A2,ROW('Historic Sales'!$O$1:$O$1000))),1) Commit that array formula by pressing [Ctrl]+[Shifr]+[Enter] ALSO: If you have more that 1000 rows of sales data, change the 1000 to whatever is appropriate. If that works, copy the formula into subsequent cells. I hope that helps. Regards, Ron |
#3
|
|||
|
|||
Hi
Add into products table a column for last price change date. Now, when on sales sheet dates start from A2, products start from B2, and prices start from C2, and last row is 1000, and when on products sheet products start from A2, LastChangeDate from B2 and LastPrice from C2, then: Into cell B2 on products sheet enter array (with Ctrl+Shift+Enter) formula like =MAX((Sales!$B$2:$B$1000=$A2)*(Sales!$A$2:$A$1000) ) Into cell C2 on products sheet enter formula like =SUMPRODUCT(--(Sales!$A$2:$A$100=$B2),--(Sales$B$2:$B$1000=$A2),Salesa!$C$2: $C$1000) Copy formulas down on products sheet -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "samantha" wrote in message ... Hi thanks for the reply. I tried including the entire table, but the error now is N/A for all the entries.. the problem is.. i have a small list of products and I want to know their sales price(the last time they were sold). I have a big excel sheet with historic sales data in seperate worksheet. Now, I want to use vlookup to find the sales price of the list of products. the sales proce is in worksheet'historic_sales' in coloumn 'P'. The small list of products whose prices i need are in worksheet 'list'. Hope i made my problem clear. thanks a lot for a help cheers, samantha |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
I think I can, but where? | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Charting challenge/query | Charts and Charting in Excel |