Lookup dates and values

[Suan]

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

 

[Max]

Source table as posted assumed in sheet: B, within A15
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))
where:
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

 

[Max]

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
---

 

[smartin]

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 B25
-SheetA "Date" value is in B1 and desired "Training" value is in B4.
Paste the formula in B4 and fill down.

 

[Suan]

Doesnt seem to work, its returning an #N/A error??

 

[Max]

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
---