Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative Worksheet references
Is there a way around the fact that you cannot use a relative reference for
the Worksheet? I want to use the ending balance from June to automatically populate into the July opening balance. I have a new worksheet for each month. The formulas copy and update except for the prior worksheet reference. Is there a way to get the worksheet to look at the one before it to get these figures? Right now I use =June!E9 for the July beginning balance, but when I copy the worksheet to make one for August the formula stays =June!E9. I would like it to update to =July!E9 (or whatever it takes to get that data) without having to go through and manually change each line item. Thanks for any input. I have been browsing the questions and answers here and realize that I knew Office 97 pretty well, but am totally lost in Office 07! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative Worksheet references
Here is one way...using INDIRECT()
Assuming in each sheet you have a header or a cell where you have mentioned any date of the current month...Suppose cell A1 always hold a date of the current month. Try this formula...From your post I understand your sheet names are in the format June,July,August etc; hence "mmmm". If Jun,Jul,Aug you need to change that to "mmm". If year is refered you can change this to "mmmyy". Try and feedback. =INDIRECT(TEXT(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)),"mmmm") & "!E9") The above formula should refer to the previous month E9 If this post helps click Yes --------------- Jacob Skaria "mjb1" wrote: Is there a way around the fact that you cannot use a relative reference for the Worksheet? I want to use the ending balance from June to automatically populate into the July opening balance. I have a new worksheet for each month. The formulas copy and update except for the prior worksheet reference. Is there a way to get the worksheet to look at the one before it to get these figures? Right now I use =June!E9 for the July beginning balance, but when I copy the worksheet to make one for August the formula stays =June!E9. I would like it to update to =July!E9 (or whatever it takes to get that data) without having to go through and manually change each line item. Thanks for any input. I have been browsing the questions and answers here and realize that I knew Office 97 pretty well, but am totally lost in Office 07! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative Worksheet references
Thanks Jacob,
It works great for copy the formula from worksheet to worksheet, but not down the rows within the worksheet. Entering the formula once on 50 lines will be a lot easier than having to correct it for all 50 lines on each months sheet. Unless you have another trick up your sleeve that will take care of that too! :) I did change my worksheet names to the "mmm" format to make it simpler. Thanks again for your help. "Jacob Skaria" wrote: Here is one way...using INDIRECT() Assuming in each sheet you have a header or a cell where you have mentioned any date of the current month...Suppose cell A1 always hold a date of the current month. Try this formula...From your post I understand your sheet names are in the format June,July,August etc; hence "mmmm". If Jun,Jul,Aug you need to change that to "mmm". If year is refered you can change this to "mmmyy". Try and feedback. =INDIRECT(TEXT(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)),"mmmm") & "!E9") The above formula should refer to the previous month E9 If this post helps click Yes --------------- Jacob Skaria "mjb1" wrote: Is there a way around the fact that you cannot use a relative reference for the Worksheet? I want to use the ending balance from June to automatically populate into the July opening balance. I have a new worksheet for each month. The formulas copy and update except for the prior worksheet reference. Is there a way to get the worksheet to look at the one before it to get these figures? Right now I use =June!E9 for the July beginning balance, but when I copy the worksheet to make one for August the formula stays =June!E9. I would like it to update to =July!E9 (or whatever it takes to get that data) without having to go through and manually change each line item. Thanks for any input. I have been browsing the questions and answers here and realize that I knew Office 97 pretty well, but am totally lost in Office 07! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative Worksheet references
Try the below formula which will refer to E1 and when you copy down change to
E2,E3 and so on..The trick here is use the ROW function.. If you want to refer the same ROW then use ROW() instead of ROW(A1).. =INDIRECT(TEXT(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)),"mmmm") & "!E" & ROW(A1)) You should be able to adjust this by changing the refenence cell within ...ROW() If this post helps click Yes --------------- Jacob Skaria "mjb1" wrote: Thanks Jacob, It works great for copy the formula from worksheet to worksheet, but not down the rows within the worksheet. Entering the formula once on 50 lines will be a lot easier than having to correct it for all 50 lines on each months sheet. Unless you have another trick up your sleeve that will take care of that too! :) I did change my worksheet names to the "mmm" format to make it simpler. Thanks again for your help. "Jacob Skaria" wrote: Here is one way...using INDIRECT() Assuming in each sheet you have a header or a cell where you have mentioned any date of the current month...Suppose cell A1 always hold a date of the current month. Try this formula...From your post I understand your sheet names are in the format June,July,August etc; hence "mmmm". If Jun,Jul,Aug you need to change that to "mmm". If year is refered you can change this to "mmmyy". Try and feedback. =INDIRECT(TEXT(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)),"mmmm") & "!E9") The above formula should refer to the previous month E9 If this post helps click Yes --------------- Jacob Skaria "mjb1" wrote: Is there a way around the fact that you cannot use a relative reference for the Worksheet? I want to use the ending balance from June to automatically populate into the July opening balance. I have a new worksheet for each month. The formulas copy and update except for the prior worksheet reference. Is there a way to get the worksheet to look at the one before it to get these figures? Right now I use =June!E9 for the July beginning balance, but when I copy the worksheet to make one for August the formula stays =June!E9. I would like it to update to =July!E9 (or whatever it takes to get that data) without having to go through and manually change each line item. Thanks for any input. I have been browsing the questions and answers here and realize that I knew Office 97 pretty well, but am totally lost in Office 07! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative Worksheet references
Thank-you Jacob,
It works like a charm!!! :) "Jacob Skaria" wrote: Try the below formula which will refer to E1 and when you copy down change to E2,E3 and so on..The trick here is use the ROW function.. If you want to refer the same ROW then use ROW() instead of ROW(A1).. =INDIRECT(TEXT(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)),"mmmm") & "!E" & ROW(A1)) You should be able to adjust this by changing the refenence cell within ..ROW() If this post helps click Yes --------------- Jacob Skaria "mjb1" wrote: Thanks Jacob, It works great for copy the formula from worksheet to worksheet, but not down the rows within the worksheet. Entering the formula once on 50 lines will be a lot easier than having to correct it for all 50 lines on each months sheet. Unless you have another trick up your sleeve that will take care of that too! :) I did change my worksheet names to the "mmm" format to make it simpler. Thanks again for your help. "Jacob Skaria" wrote: Here is one way...using INDIRECT() Assuming in each sheet you have a header or a cell where you have mentioned any date of the current month...Suppose cell A1 always hold a date of the current month. Try this formula...From your post I understand your sheet names are in the format June,July,August etc; hence "mmmm". If Jun,Jul,Aug you need to change that to "mmm". If year is refered you can change this to "mmmyy". Try and feedback. =INDIRECT(TEXT(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)),"mmmm") & "!E9") The above formula should refer to the previous month E9 If this post helps click Yes --------------- Jacob Skaria "mjb1" wrote: Is there a way around the fact that you cannot use a relative reference for the Worksheet? I want to use the ending balance from June to automatically populate into the July opening balance. I have a new worksheet for each month. The formulas copy and update except for the prior worksheet reference. Is there a way to get the worksheet to look at the one before it to get these figures? Right now I use =June!E9 for the July beginning balance, but when I copy the worksheet to make one for August the formula stays =June!E9. I would like it to update to =July!E9 (or whatever it takes to get that data) without having to go through and manually change each line item. Thanks for any input. I have been browsing the questions and answers here and realize that I knew Office 97 pretty well, but am totally lost in Office 07! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
Excel: Relative worksheet references? | Excel Discussion (Misc queries) | |||
Relative references | Excel Worksheet Functions | |||
Relative references | Excel Discussion (Misc queries) |