Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking two worksheets
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking two worksheets
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,IND IRECT("'"&C$1&"'!A:A"),0),$A$1+2) Copy down and across as required -- Regards Roger Govier "Sam" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking two worksheets
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" wrote: 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,IND IRECT("'"&C$1&"'!A:A"),0),$A$1+2) Copy down and across as required -- Regards Roger Govier "Sam" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking two worksheets
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 -- Regards Roger Govier "Sam" wrote in message ... 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" wrote: 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,IND IRECT("'"&C$1&"'!A:A"),0),$A$1+2) Copy down and across as required -- Regards Roger Govier "Sam" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking worksheets | Excel Discussion (Misc queries) | |||
Linking worksheets | Excel Worksheet Functions | |||
linking worksheets | Excel Discussion (Misc queries) | |||
Linking worksheets | Excel Discussion (Misc queries) | |||
Linking worksheets | Links and Linking in Excel |