If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
data associated with latest date
I have two columns. Column one has the dates of the month and the second
column has inventory amounts for that date. The inventory amounts is paste linked to another worksheet. I have the data in column 2 to show up in the linked worksheet for the latest date. I am using =max(date1...date31) to find the lasted date. Any suggestions to transfer the associated data to the linked field would be greatly appreciated |
#2
|
|||
|
|||
data associated with latest date
something like:
=LOOKUP(MAX(date1....date31),A2:B31) Where A2:B31 is the table of info in your first worksheet. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "tcek" wrote: I have two columns. Column one has the dates of the month and the second column has inventory amounts for that date. The inventory amounts is paste linked to another worksheet. I have the data in column 2 to show up in the linked worksheet for the latest date. I am using =max(date1...date31) to find the lasted date. Any suggestions to transfer the associated data to the linked field would be greatly appreciated |
#3
|
|||
|
|||
data associated with latest date
Indicatively: =INDEX(Col2,MATCH(Max(Col1),Col1,0))
where Col1 = Dates Col2 = Inventory amounts -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "tcek" wrote: I have two columns. Column one has the dates of the month and the second column has inventory amounts for that date. The inventory amounts is paste linked to another worksheet. I have the data in column 2 to show up in the linked worksheet for the latest date. I am using =max(date1...date31) to find the lasted date. Any suggestions to transfer the associated data to the linked field would be greatly appreciated |
#4
|
|||
|
|||
data associated with latest date
Do i acutally use "Col2" and "Col1" or the column titles in row 1? would the
Col1 in the max statement have the row range included? "Max" wrote: Indicatively: =INDEX(Col2,MATCH(Max(Col1),Col1,0)) where Col1 = Dates Col2 = Inventory amounts -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "tcek" wrote: I have two columns. Column one has the dates of the month and the second column has inventory amounts for that date. The inventory amounts is paste linked to another worksheet. I have the data in column 2 to show up in the linked worksheet for the latest date. I am using =max(date1...date31) to find the lasted date. Any suggestions to transfer the associated data to the linked field would be greatly appreciated |
#5
|
|||
|
|||
data associated with latest date
"Indicatively" means that these would be your ranges in the expression
Example Col1 = dates range, eg: A2:A30 Col2 = corresponding inventory amounts range, eg: B2:B30 Hence: =INDEX(Col2,MATCH(Max(Col1),Col1,0)) would be actually this: =INDEX(B2:B30,MATCH(Max(A2:A30),A2:A30,0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "tcek" wrote: Do i actually use "Col2" and "Col1" or the column titles in row 1? would the Col1 in the max statement have the row range included? |
#6
|
|||
|
|||
data associated with latest date
Fantastic..thanks
"Max" wrote: "Indicatively" means that these would be your ranges in the expression Example Col1 = dates range, eg: A2:A30 Col2 = corresponding inventory amounts range, eg: B2:B30 Hence: =INDEX(Col2,MATCH(Max(Col1),Col1,0)) would be actually this: =INDEX(B2:B30,MATCH(Max(A2:A30),A2:A30,0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "tcek" wrote: Do i actually use "Col2" and "Col1" or the column titles in row 1? would the Col1 in the max statement have the row range included? |
Thread Tools | |
Display Modes | |
|
|