Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with multiple values
Hello,
I have 2 worksheets. One with historical rents: it has 5 columns: A B C D E Store # active rent Monthly Rent Rent Start date Annual rent 1 500 active 1,734 5/9/2003 20,809 2 500 active 2,365 6/1/2003 28,376 3 500 active 2,512 6/1/2008 29,263 4 501 active 4,411 3/4/1998 52,936 The second spreadsheet is organized like an income stmt with monthly financial data. if i change the store # in cell D4, it will update the entire sheets income stmt showing financial data for the entire sheet. i'm trying to show historical rent, so it will show rent that was paid that month... currently whenever i change the rent, from the sheet that has the current rent it will change everything in the past, so i just want it to show the actual rent that was paid. Any help would be great! Thanks! David |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with multiple values
It's not real clear what you're wanting to do. Can you rephrase it?
-- Biff Microsoft Excel MVP "David" wrote in message ... Hello, I have 2 worksheets. One with historical rents: it has 5 columns: A B C D E Store # active rent Monthly Rent Rent Start date Annual rent 1 500 active 1,734 5/9/2003 20,809 2 500 active 2,365 6/1/2003 28,376 3 500 active 2,512 6/1/2008 29,263 4 501 active 4,411 3/4/1998 52,936 The second spreadsheet is organized like an income stmt with monthly financial data. if i change the store # in cell D4, it will update the entire sheets income stmt showing financial data for the entire sheet. i'm trying to show historical rent, so it will show rent that was paid that month... currently whenever i change the rent, from the sheet that has the current rent it will change everything in the past, so i just want it to show the actual rent that was paid. Any help would be great! Thanks! David |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with multiple values
I'm trying to show what rent was paid in a specific month and year. So, if
rent was $1,000 in March of 2007 and it changes to $1,200 in March of 2008. So, if i have a list of one store with changing rent, i want it to correspond with the date. I thought of a sumproduct or an index... Maybe, i shouldn't have used vlookup as the subject. Let me know if this helps or if you need more clarification. Thanks! ~ David "T. Valko" wrote: It's not real clear what you're wanting to do. Can you rephrase it? -- Biff Microsoft Excel MVP "David" wrote in message ... Hello, I have 2 worksheets. One with historical rents: it has 5 columns: A B C D E Store # active rent Monthly Rent Rent Start date Annual rent 1 500 active 1,734 5/9/2003 20,809 2 500 active 2,365 6/1/2003 28,376 3 500 active 2,512 6/1/2008 29,263 4 501 active 4,411 3/4/1998 52,936 The second spreadsheet is organized like an income stmt with monthly financial data. if i change the store # in cell D4, it will update the entire sheets income stmt showing financial data for the entire sheet. i'm trying to show historical rent, so it will show rent that was paid that month... currently whenever i change the rent, from the sheet that has the current rent it will change everything in the past, so i just want it to show the actual rent that was paid. Any help would be great! Thanks! David |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with multiple values
You can use SUMPRODUCT for that as long as there aren't multiple rents
listed for the same month/year. Something like this: =SUMPRODUCT(--(A2:A10=store_number),--(D2:D10=rent_date),C2:C10) However, I'm guessing you want something different because the rent dates have gaps. Do you really want the most recent rent? -- Biff Microsoft Excel MVP "David" wrote in message ... I'm trying to show what rent was paid in a specific month and year. So, if rent was $1,000 in March of 2007 and it changes to $1,200 in March of 2008. So, if i have a list of one store with changing rent, i want it to correspond with the date. I thought of a sumproduct or an index... Maybe, i shouldn't have used vlookup as the subject. Let me know if this helps or if you need more clarification. Thanks! ~ David "T. Valko" wrote: It's not real clear what you're wanting to do. Can you rephrase it? -- Biff Microsoft Excel MVP "David" wrote in message ... Hello, I have 2 worksheets. One with historical rents: it has 5 columns: A B C D E Store # active rent Monthly Rent Rent Start date Annual rent 1 500 active 1,734 5/9/2003 20,809 2 500 active 2,365 6/1/2003 28,376 3 500 active 2,512 6/1/2008 29,263 4 501 active 4,411 3/4/1998 52,936 The second spreadsheet is organized like an income stmt with monthly financial data. if i change the store # in cell D4, it will update the entire sheets income stmt showing financial data for the entire sheet. i'm trying to show historical rent, so it will show rent that was paid that month... currently whenever i change the rent, from the sheet that has the current rent it will change everything in the past, so i just want it to show the actual rent that was paid. Any help would be great! Thanks! David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup multiple values - sum returned values into single cell | Excel Worksheet Functions | |||
Vlookup multiple criteria multiple occurrences sum values | Excel Worksheet Functions | |||
vlookup on multiple values | Excel Worksheet Functions | |||
vlookup, multiple values, sum values into one cell?? | Excel Worksheet Functions | |||
VLookup for multiple values!! | Excel Worksheet Functions |