Linking two worksheets

[Sam]

I have a workbook that has several worksheets. One worksheet will be a
summary of the data on the other worksheets. The other worksheets are split
by cost center. They each have data with a column for account number,
account name and amount by month.

Sample data (same format for each cost center tab)
Account Description Jan Feb Mar Apr May Jun
1234 Salaries 124 333 432 876 987 78
5678 Travel 85 99 100 43 57 65


Summary worksheet - format
(Data in Summary tab should be for one month only)
Account Description Cost Center #1 Cost Center #2 Cost Center #3
1234 Salaries ? ?
?
5678 Travel ? ?
?

I know how to write a formula that will look at account and description and
put in the amount.

My Question is this: How do I write a formula that will look at the
account, description and then know to take the data from a specific month.
For example: It is April, so I want to pull only the April data into the
summary tab for each cost center. I am looking for a formula for the ? marks
above.

Thanks.

 

[Roger Govier]

Hi Sam

On the Summary sheet, I inserted a row above your headings.
In A1 I entered the Month number for April 4
In C1:xxx1 I entered the sheet names for the Costcentre

In cell C3 of Summary I entered
=INDEX(INDIRECT("'"&C$1&"'!1:65536"),MATCH($A3,INDIRECT("'"&C$1&"'!A:A"),0),$A$1+2)
Copy down and across as required

 

[Sam]

Hi Roger.

Thank you for the response. I am not quite there. Here is more information.

In addition to being able to pull data by a specific month, I need to match
the data in column A and column B of Summary to column A and column B of the
Cost Center tabs and bring back the data for the column matching the month as
indicated in cell A1 of Summary (based on your response).

Note. The period headers are Actual Jan, Acutal Feb, etc. They are not
just the month January, February, etc or a number 4 for April. I can not
change this as the data flows from another system.

The data on the summary tab will start in E8, so that is where I want to
start the formula.

An example of the Cost Center tab name would be Executive by Month or Sales
by Month.

Based on this, how would I change the formula?

Thanks again for your work on this.

 

[Roger Govier]

Hi Sam

It doesn't matter what the the columns are called. we are only using the 34
to represent the column number of the data.
Since the month number (1 for Jan) starts in column3, we must add 2 to the
value you say you wish to return.

Surely the same Account has the same Description, so it should not matter
that we are only matching on account Number.

If you are still having problems, mail me the book
To mail direct, send to
roger at technology4u dot co dot uk

make the obvious change with at and dot