Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto filling relative worksheet references
I think I might be missing something simple, here...
I have 13 worksheets labeled jan-03 to dec-03 and 'totals for 03'. On the totals sheet I have a list of months in the first column and next to each month I want to carry over a total for each month's sheet. (I'm simplifying this a little as I have a number of totals on each month's sheet to carry over). It's getting very tedious typing in every 3d cell reference and I wanted to use autofill. Can autofill update a worksheet reference from cell to cell as it would a cell reference on the same sheet e.g. I start on a cell containing '=jan-03!$A$1' and as I grab the handle and pull down over 11 additional cells each fills with '=feb-03!A$!$' etc? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto filling relative worksheet references
I should also add that I'm unfamiliar with macros and visual basic so if
this is the only answer I'll need my hand holding! "John Constable" wrote in message ... I think I might be missing something simple, here... I have 13 worksheets labeled jan-03 to dec-03 and 'totals for 03'. On the totals sheet I have a list of months in the first column and next to each month I want to carry over a total for each month's sheet. (I'm simplifying this a little as I have a number of totals on each month's sheet to carry over). It's getting very tedious typing in every 3d cell reference and I wanted to use autofill. Can autofill update a worksheet reference from cell to cell as it would a cell reference on the same sheet e.g. I start on a cell containing '=jan-03!$A$1' and as I grab the handle and pull down over 11 additional cells each fills with '=feb-03!A$!$' etc? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto filling relative worksheet references
John,
Here is an example that assumes that A2:A13 on 'totals-03' holds the values jan, feb, etc, and picks up the values from A1 on the monthly sheets =INDIRECT("'"&A2&"-03'!A1") just copy down, and build to suit -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "John Constable" wrote in message ... I should also add that I'm unfamiliar with macros and visual basic so if this is the only answer I'll need my hand holding! "John Constable" wrote in message ... I think I might be missing something simple, here... I have 13 worksheets labeled jan-03 to dec-03 and 'totals for 03'. On the totals sheet I have a list of months in the first column and next to each month I want to carry over a total for each month's sheet. (I'm simplifying this a little as I have a number of totals on each month's sheet to carry over). It's getting very tedious typing in every 3d cell reference and I wanted to use autofill. Can autofill update a worksheet reference from cell to cell as it would a cell reference on the same sheet e.g. I start on a cell containing '=jan-03!$A$1' and as I grab the handle and pull down over 11 additional cells each fills with '=feb-03!A$!$' etc? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto filling relative worksheet references
use the indirect function and build the reference with a formula
=indirect(format(Date(2003,row(A1),1)."mmm-yy" & "!A1")) row(A1) is used to generate an integer (1 to 12) to indicate the month. -- Regards, Tom Ogilvy "John Constable" wrote in message ... I think I might be missing something simple, here... I have 13 worksheets labeled jan-03 to dec-03 and 'totals for 03'. On the totals sheet I have a list of months in the first column and next to each month I want to carry over a total for each month's sheet. (I'm simplifying this a little as I have a number of totals on each month's sheet to carry over). It's getting very tedious typing in every 3d cell reference and I wanted to use autofill. Can autofill update a worksheet reference from cell to cell as it would a cell reference on the same sheet e.g. I start on a cell containing '=jan-03!$A$1' and as I grab the handle and pull down over 11 additional cells each fills with '=feb-03!A$!$' etc? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto filling relative worksheet references
Thanks, everyone - a working solution is now in place.
"Tom Ogilvy" wrote in message ... use the indirect function and build the reference with a formula =indirect(format(Date(2003,row(A1),1)."mmm-yy" & "!A1")) row(A1) is used to generate an integer (1 to 12) to indicate the month. -- Regards, Tom Ogilvy "John Constable" wrote in message ... I think I might be missing something simple, here... I have 13 worksheets labeled jan-03 to dec-03 and 'totals for 03'. On the totals sheet I have a list of months in the first column and next to each month I want to carry over a total for each month's sheet. (I'm simplifying this a little as I have a number of totals on each month's sheet to carry over). It's getting very tedious typing in every 3d cell reference and I wanted to use autofill. Can autofill update a worksheet reference from cell to cell as it would a cell reference on the same sheet e.g. I start on a cell containing '=jan-03!$A$1' and as I grab the handle and pull down over 11 additional cells each fills with '=feb-03!A$!$' etc? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative Worksheet references | Excel Worksheet Functions | |||
Filling in worksheet cell references | Excel Discussion (Misc queries) | |||
Auto filling data according to seperate worksheet | Excel Discussion (Misc queries) | |||
Excel: Relative worksheet references? | Excel Discussion (Misc queries) | |||
VB Scripting/Macro - Filling/Editing Worksheet References | New Users to Excel |