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

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

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

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.

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

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