Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup dates and values
Hi
I want to set up a formula that allows me to enter a date on spreadsheet A(eg May-09) and in Spreadsheet A also have a month actual column for various expenses(expense type & amounts) In spread sheet B I have actual data by month for a whole year by various expenditure types. I would like to in spreadsheet A Actual column pick up the various expenses for that particular month entered in Spreadsheet A (May-09) from spread sheet B for that month. And also every time I change the month(in spreadsheet A) it picks up the relevant months expenses from sheet B!!! eg SheetA Date: May-09 Actual Training $600 Salaries $5000 Consultants$800 Materials $600 Sheet B May-09 June-09 Jul-09 Training $600 $1000 $1400 Salaries $5000 $5500 $5500 Consultants$800 $1500 $1000 Materials $600 $1000 $800 -- SC |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup dates and values
Source table as posted assumed in sheet: B, within A1:D5
In "A", Place in B4, copied down to B7: =INDEX(B!$B$2:$D$5,MATCH(A4,B!$A$2:$A$5,0),MATCH(B $1,B!$B$1:$D$1,0)) whe A4 down contains the row headers, eg: Training, Salaries, etc B1 contains the "month/year" (this data is assumed consistent* with what you have reflected as the "month/year" in B's A2 across) *if its a text-string in the source, it must be the same text-string in B1. Likewise, if its a 1st-of-month real date (formatted as: mmm-yy), it must be the same data in B1. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Suan" wrote: I want to set up a formula that allows me to enter a date on spreadsheet A(eg May-09) and in Spreadsheet A also have a month actual column for various expenses(expense type & amounts) In spread sheet B I have actual data by month for a whole year by various expenditure types. I would like to in spreadsheet A Actual column pick up the various expenses for that particular month entered in Spreadsheet A (May-09) from spread sheet B for that month. And also every time I change the month(in spreadsheet A) it picks up the relevant months expenses from sheet B!!! eg SheetA Date: May-09 Actual Training $600 Salaries $5000 Consultants$800 Materials $600 Sheet B May-09 June-09 Jul-09 Training $600 $1000 $1400 Salaries $5000 $5500 $5500 Consultants$800 $1500 $1000 Materials $600 $1000 $800 -- SC |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup dates and values
Errata
.. reflected as the "month/year" in B's A2 across should read: in B's B1 across -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup dates and values
Suan wrote:
Hi I want to set up a formula that allows me to enter a date on spreadsheet A(eg May-09) and in Spreadsheet A also have a month actual column for various expenses(expense type & amounts) In spread sheet B I have actual data by month for a whole year by various expenditure types. I would like to in spreadsheet A Actual column pick up the various expenses for that particular month entered in Spreadsheet A (May-09) from spread sheet B for that month. And also every time I change the month(in spreadsheet A) it picks up the relevant months expenses from sheet B!!! eg SheetA Date: May-09 Actual Training $600 Salaries $5000 Consultants$800 Materials $600 Sheet B May-09 June-09 Jul-09 Training $600 $1000 $1400 Salaries $5000 $5500 $5500 Consultants$800 $1500 $1000 Materials $600 $1000 $800 =INDEX(SheetB!$B$2:$D$5,MATCH(SheetA!$A4,SheetB!$A $2:$A$5,0),MATCH(SheetA!$B$1,SheetB!$B$1:$D$1,0)) Assumes -SheetA and SheetB are in the same workbook -SheetB has labels in row 1 and column A, data in B2:D5 -SheetA "Date" value is in B1 and desired "Training" value is in B4. Paste the formula in B4 and fill down. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup dates and values
Doesnt seem to work, its returning an #N/A error??
-- SC "Max" wrote: Source table as posted assumed in sheet: B, within A1:D5 In "A", Place in B4, copied down to B7: =INDEX(B!$B$2:$D$5,MATCH(A4,B!$A$2:$A$5,0),MATCH(B $1,B!$B$1:$D$1,0)) whe A4 down contains the row headers, eg: Training, Salaries, etc B1 contains the "month/year" (this data is assumed consistent* with what you have reflected as the "month/year" in B's A2 across) *if its a text-string in the source, it must be the same text-string in B1. Likewise, if its a 1st-of-month real date (formatted as: mmm-yy), it must be the same data in B1. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Suan" wrote: I want to set up a formula that allows me to enter a date on spreadsheet A(eg May-09) and in Spreadsheet A also have a month actual column for various expenses(expense type & amounts) In spread sheet B I have actual data by month for a whole year by various expenditure types. I would like to in spreadsheet A Actual column pick up the various expenses for that particular month entered in Spreadsheet A (May-09) from spread sheet B for that month. And also every time I change the month(in spreadsheet A) it picks up the relevant months expenses from sheet B!!! eg SheetA Date: May-09 Actual Training $600 Salaries $5000 Consultants$800 Materials $600 Sheet B May-09 June-09 Jul-09 Training $600 $1000 $1400 Salaries $5000 $5500 $5500 Consultants$800 $1500 $1000 Materials $600 $1000 $800 -- SC |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup dates and values
"Suan" wrote:
Doesnt seem to work, its returning an #N/A error?? Well, it should have, since the expression's essentially identical to the one offered by Smartin which you got to work, except for the assumptions made on the sheetnames and where the lookup values are, etc. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i wish to lookup values in column A, & add adjacent values in colu | Excel Discussion (Misc queries) | |||
Lookup between dates | Excel Discussion (Misc queries) | |||
How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) |