Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a 'dynamic' formula?
Can anyone please help with this function?
I have a large spreadsheet file with numerous worksheets. Each sheet represents one month of the year and is named Jan05, Feb05, Mar05 etc. In the "Summary" sheet I want to automatically reference a range in the other sheets. For example: In the Summary sheet column A, I have the sheet names: Jan05 Feb05 Mar05 etc. In column B, (let's use B5 as an example) I want a formula that reads something like: =B4 + Apr05!C7 This seems simple enough, but how can I write a formula that 'automatically' enters the sheet name (i.e. "Apr05") in the formula? The row containing the data for Apr05 is only created when that month comes along, in order to keep the file size as small as possible at any time. Thanks, Coober |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a 'dynamic' formula?
use INDIRECT
HTH -- AP "Bill" a écrit dans le message de ... Can anyone please help with this function? I have a large spreadsheet file with numerous worksheets. Each sheet represents one month of the year and is named Jan05, Feb05, Mar05 etc. In the "Summary" sheet I want to automatically reference a range in the other sheets. For example: In the Summary sheet column A, I have the sheet names: Jan05 Feb05 Mar05 etc. In column B, (let's use B5 as an example) I want a formula that reads something like: =B4 + Apr05!C7 This seems simple enough, but how can I write a formula that 'automatically' enters the sheet name (i.e. "Apr05") in the formula? The row containing the data for Apr05 is only created when that month comes along, in order to keep the file size as small as possible at any time. Thanks, Coober |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a 'dynamic' formula?
I've tried using INDIRECT, but cannot find the correct syntax to make the
formula work properly. If possible, could you please be a little more specific? Thanks "Ardus Petus" wrote in message ... use INDIRECT HTH -- AP "Bill" a écrit dans le message de ... Can anyone please help with this function? I have a large spreadsheet file with numerous worksheets. Each sheet represents one month of the year and is named Jan05, Feb05, Mar05 etc. In the "Summary" sheet I want to automatically reference a range in the other sheets. For example: In the Summary sheet column A, I have the sheet names: Jan05 Feb05 Mar05 etc. In column B, (let's use B5 as an example) I want a formula that reads something like: =B4 + Apr05!C7 This seems simple enough, but how can I write a formula that 'automatically' enters the sheet name (i.e. "Apr05") in the formula? The row containing the data for Apr05 is only created when that month comes along, in order to keep the file size as small as possible at any time. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a 'dynamic' formula?
=B4 + INDIRECT(A4&"!C7")
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bill" wrote in message ... I've tried using INDIRECT, but cannot find the correct syntax to make the formula work properly. If possible, could you please be a little more specific? Thanks "Ardus Petus" wrote in message ... use INDIRECT HTH -- AP "Bill" a écrit dans le message de ... Can anyone please help with this function? I have a large spreadsheet file with numerous worksheets. Each sheet represents one month of the year and is named Jan05, Feb05, Mar05 etc. In the "Summary" sheet I want to automatically reference a range in the other sheets. For example: In the Summary sheet column A, I have the sheet names: Jan05 Feb05 Mar05 etc. In column B, (let's use B5 as an example) I want a formula that reads something like: =B4 + Apr05!C7 This seems simple enough, but how can I write a formula that 'automatically' enters the sheet name (i.e. "Apr05") in the formula? The row containing the data for Apr05 is only created when that month comes along, in order to keep the file size as small as possible at any time. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a 'dynamic' formula?
Try this:
=B4+INDIRECT(A4&"!C7") With Apr05 in A4. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bill" wrote in message ... I've tried using INDIRECT, but cannot find the correct syntax to make the formula work properly. If possible, could you please be a little more specific? Thanks "Ardus Petus" wrote in message ... use INDIRECT HTH -- AP "Bill" a écrit dans le message de ... Can anyone please help with this function? I have a large spreadsheet file with numerous worksheets. Each sheet represents one month of the year and is named Jan05, Feb05, Mar05 etc. In the "Summary" sheet I want to automatically reference a range in the other sheets. For example: In the Summary sheet column A, I have the sheet names: Jan05 Feb05 Mar05 etc. In column B, (let's use B5 as an example) I want a formula that reads something like: =B4 + Apr05!C7 This seems simple enough, but how can I write a formula that 'automatically' enters the sheet name (i.e. "Apr05") in the formula? The row containing the data for Apr05 is only created when that month comes along, in order to keep the file size as small as possible at any time. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a 'dynamic' formula?
Thanks, that works well! However, I also need to SUM a range which would
include 2 INDIRECT references: In this case, I would need to have the two references inside a SUM function. I tried this: =SUM(INDIRECT(A2&".B2"):INDIRECT(A4&".B2")) where A2 contains Jan06 & A4 contains Mar06 and a few variations, but only get an error. The idea here is to sum the values in Jan06!B2:Mar06!B2 Is this possible? Thanks "Ragdyer" wrote in message ... Try this: =B4+INDIRECT(A4&"!C7") With Apr05 in A4. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bill" wrote in message ... I've tried using INDIRECT, but cannot find the correct syntax to make the formula work properly. If possible, could you please be a little more specific? Thanks "Ardus Petus" wrote in message ... use INDIRECT HTH -- AP "Bill" a écrit dans le message de ... Can anyone please help with this function? I have a large spreadsheet file with numerous worksheets. Each sheet represents one month of the year and is named Jan05, Feb05, Mar05 etc. In the "Summary" sheet I want to automatically reference a range in the other sheets. For example: In the Summary sheet column A, I have the sheet names: Jan05 Feb05 Mar05 etc. In column B, (let's use B5 as an example) I want a formula that reads something like: =B4 + Apr05!C7 This seems simple enough, but how can I write a formula that 'automatically' enters the sheet name (i.e. "Apr05") in the formula? The row containing the data for Apr05 is only created when that month comes along, in order to keep the file size as small as possible at any time. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a 'dynamic' formula?
=INDIRECT(A2&"!B2")+INDIRECT(A4&"!B2")
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bill" wrote in message ... Thanks, that works well! However, I also need to SUM a range which would include 2 INDIRECT references: In this case, I would need to have the two references inside a SUM function. I tried this: =SUM(INDIRECT(A2&".B2"):INDIRECT(A4&".B2")) where A2 contains Jan06 & A4 contains Mar06 and a few variations, but only get an error. The idea here is to sum the values in Jan06!B2:Mar06!B2 Is this possible? Thanks "Ragdyer" wrote in message ... Try this: =B4+INDIRECT(A4&"!C7") With Apr05 in A4. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bill" wrote in message ... I've tried using INDIRECT, but cannot find the correct syntax to make the formula work properly. If possible, could you please be a little more specific? Thanks "Ardus Petus" wrote in message ... use INDIRECT HTH -- AP "Bill" a écrit dans le message de ... Can anyone please help with this function? I have a large spreadsheet file with numerous worksheets. Each sheet represents one month of the year and is named Jan05, Feb05, Mar05 etc. In the "Summary" sheet I want to automatically reference a range in the other sheets. For example: In the Summary sheet column A, I have the sheet names: Jan05 Feb05 Mar05 etc. In column B, (let's use B5 as an example) I want a formula that reads something like: =B4 + Apr05!C7 This seems simple enough, but how can I write a formula that 'automatically' enters the sheet name (i.e. "Apr05") in the formula? The row containing the data for Apr05 is only created when that month comes along, in order to keep the file size as small as possible at any time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
How do I create a 26 'tier' IF formula? | Excel Discussion (Misc queries) | |||
need to create a formula to create a timesheet but haven't a clue | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |