| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Systematically updating references to other workbooks
I produce a monthly report on investments where the ending balance of an
account is reported in one column and the interest earned is reported in another column. The data for this report comes from another workbook, so each entry is linked to this other workbook. Since I produce this report monthly, I would like to be able to update those links automatically rather than manually. For example, in month 1, the link would be 1991ABC$L$268. In month 2 the link would need to be udpated to 1991ABC$L$269, month 3 1991ABC$L$270, etc. Thanks -- Allan |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Systematically updating references to other workbooks
I presume your destination data is straight down the row.
In that case, delete the $ prior to your row number, and the row number will automatically increase as you go down your rows. "Flipper" wrote: I produce a monthly report on investments where the ending balance of an account is reported in one column and the interest earned is reported in another column. The data for this report comes from another workbook, so each entry is linked to this other workbook. Since I produce this report monthly, I would like to be able to update those links automatically rather than manually. For example, in month 1, the link would be 1991ABC$L$268. In month 2 the link would need to be udpated to 1991ABC$L$269, month 3 1991ABC$L$270, etc. Thanks -- Allan |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Systematically updating references to other workbooks
That won't work as the destination cell remains the same from month to month
-- Allan "Sean Timmons" wrote: I presume your destination data is straight down the row. In that case, delete the $ prior to your row number, and the row number will automatically increase as you go down your rows. "Flipper" wrote: I produce a monthly report on investments where the ending balance of an account is reported in one column and the interest earned is reported in another column. The data for this report comes from another workbook, so each entry is linked to this other workbook. Since I produce this report monthly, I would like to be able to update those links automatically rather than manually. For example, in month 1, the link would be 1991ABC$L$268. In month 2 the link would need to be udpated to 1991ABC$L$269, month 3 1991ABC$L$270, etc. Thanks -- Allan |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Systematically updating references to other workbooks
Oh! OK.. Then
=Indirect("1991ABC$L$26"&month(now())) Would return the value in 1991ABC$L$265 so.. if you wanted 268 instead, just make it =Indirect("1991ABC$L$26"&month(now())+3) "Flipper" wrote: That won't work as the destination cell remains the same from month to month -- Allan "Sean Timmons" wrote: I presume your destination data is straight down the row. In that case, delete the $ prior to your row number, and the row number will automatically increase as you go down your rows. "Flipper" wrote: I produce a monthly report on investments where the ending balance of an account is reported in one column and the interest earned is reported in another column. The data for this report comes from another workbook, so each entry is linked to this other workbook. Since I produce this report monthly, I would like to be able to update those links automatically rather than manually. For example, in month 1, the link would be 1991ABC$L$268. In month 2 the link would need to be udpated to 1991ABC$L$269, month 3 1991ABC$L$270, etc. Thanks -- Allan |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Systematically updating references to other workbooks
Sean
I'm daft. I don't understand how to use indirect. At the point that you insert "month(now()))" what does that mean? Would I actually type in, for example, May 2009, and then now, and don't get it. Can you help me with that too. Thanks -- Allan "Sean Timmons" wrote: Oh! OK.. Then =Indirect("1991ABC$L$26"&month(now())) Would return the value in 1991ABC$L$265 so.. if you wanted 268 instead, just make it =Indirect("1991ABC$L$26"&month(now())+3) "Flipper" wrote: That won't work as the destination cell remains the same from month to month -- Allan "Sean Timmons" wrote: I presume your destination data is straight down the row. In that case, delete the $ prior to your row number, and the row number will automatically increase as you go down your rows. "Flipper" wrote: I produce a monthly report on investments where the ending balance of an account is reported in one column and the interest earned is reported in another column. The data for this report comes from another workbook, so each entry is linked to this other workbook. Since I produce this report monthly, I would like to be able to update those links automatically rather than manually. For example, in month 1, the link would be 1991ABC$L$268. In month 2 the link would need to be udpated to 1991ABC$L$269, month 3 1991ABC$L$270, etc. Thanks -- Allan |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Systematically updating references to other workbooks
No. You use the formula as given.
.... or you could use =Indirect("1991ABC$L$26"&month(today())) instead of =Indirect("1991ABC$L$26"&month(now())) If there are functions which you don't understand, look them up in Excel help. now() returns current date and time today() returns current date month(...) returns the month number for the date given in the argument, so for a date in May, month(...) returns 5. -- David Biddulph "Flipper" wrote in message ... Sean I'm daft. I don't understand how to use indirect. At the point that you insert "month(now()))" what does that mean? Would I actually type in, for example, May 2009, and then now, and don't get it. Can you help me with that too. Thanks -- Allan "Sean Timmons" wrote: Oh! OK.. Then =Indirect("1991ABC$L$26"&month(now())) Would return the value in 1991ABC$L$265 so.. if you wanted 268 instead, just make it =Indirect("1991ABC$L$26"&month(now())+3) "Flipper" wrote: That won't work as the destination cell remains the same from month to month -- Allan "Sean Timmons" wrote: I presume your destination data is straight down the row. In that case, delete the $ prior to your row number, and the row number will automatically increase as you go down your rows. "Flipper" wrote: I produce a monthly report on investments where the ending balance of an account is reported in one column and the interest earned is reported in another column. The data for this report comes from another workbook, so each entry is linked to this other workbook. Since I produce this report monthly, I would like to be able to update those links automatically rather than manually. For example, in month 1, the link would be 1991ABC$L$268. In month 2 the link would need to be udpated to 1991ABC$L$269, month 3 1991ABC$L$270, etc. Thanks -- Allan |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Systematically updating references to other workbooks
when I use it a written I get "REF" so something is not right.
-- Allan "David Biddulph" wrote: No. You use the formula as given. .... or you could use =Indirect("1991ABC$L$26"&month(today())) instead of =Indirect("1991ABC$L$26"&month(now())) If there are functions which you don't understand, look them up in Excel help. now() returns current date and time today() returns current date month(...) returns the month number for the date given in the argument, so for a date in May, month(...) returns 5. -- David Biddulph "Flipper" wrote in message ... Sean I'm daft. I don't understand how to use indirect. At the point that you insert "month(now()))" what does that mean? Would I actually type in, for example, May 2009, and then now, and don't get it. Can you help me with that too. Thanks -- Allan "Sean Timmons" wrote: Oh! OK.. Then =Indirect("1991ABC$L$26"&month(now())) Would return the value in 1991ABC$L$265 so.. if you wanted 268 instead, just make it =Indirect("1991ABC$L$26"&month(now())+3) "Flipper" wrote: That won't work as the destination cell remains the same from month to month -- Allan "Sean Timmons" wrote: I presume your destination data is straight down the row. In that case, delete the $ prior to your row number, and the row number will automatically increase as you go down your rows. "Flipper" wrote: I produce a monthly report on investments where the ending balance of an account is reported in one column and the interest earned is reported in another column. The data for this report comes from another workbook, so each entry is linked to this other workbook. Since I produce this report monthly, I would like to be able to update those links automatically rather than manually. For example, in month 1, the link would be 1991ABC$L$268. In month 2 the link would need to be udpated to 1991ABC$L$269, month 3 1991ABC$L$270, etc. Thanks -- Allan |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Systematically updating references to other workbooks
Don't you need an exclamation mark after the sheet name in your reference?
You were talking about 1991ABC$L$268 but didn't you mean 1991ABC!$L$268 ? -- David Biddulph "Flipper" wrote in message ... when I use it a written I get "REF" so something is not right. -- Allan "David Biddulph" wrote: No. You use the formula as given. .... or you could use =Indirect("1991ABC$L$26"&month(today())) instead of =Indirect("1991ABC$L$26"&month(now())) If there are functions which you don't understand, look them up in Excel help. now() returns current date and time today() returns current date month(...) returns the month number for the date given in the argument, so for a date in May, month(...) returns 5. -- David Biddulph "Flipper" wrote in message ... Sean I'm daft. I don't understand how to use indirect. At the point that you insert "month(now()))" what does that mean? Would I actually type in, for example, May 2009, and then now, and don't get it. Can you help me with that too. Thanks -- Allan "Sean Timmons" wrote: Oh! OK.. Then =Indirect("1991ABC$L$26"&month(now())) Would return the value in 1991ABC$L$265 so.. if you wanted 268 instead, just make it =Indirect("1991ABC$L$26"&month(now())+3) "Flipper" wrote: That won't work as the destination cell remains the same from month to month -- Allan "Sean Timmons" wrote: I presume your destination data is straight down the row. In that case, delete the $ prior to your row number, and the row number will automatically increase as you go down your rows. "Flipper" wrote: I produce a monthly report on investments where the ending balance of an account is reported in one column and the interest earned is reported in another column. The data for this report comes from another workbook, so each entry is linked to this other workbook. Since I produce this report monthly, I would like to be able to update those links automatically rather than manually. For example, in month 1, the link would be 1991ABC$L$268. In month 2 the link would need to be udpated to 1991ABC$L$269, month 3 1991ABC$L$270, etc. Thanks -- Allan |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Systematically updating references to other workbooks
That is correct, but I'm still getting and error.
-- Allan "David Biddulph" wrote: Don't you need an exclamation mark after the sheet name in your reference? You were talking about 1991ABC$L$268 but didn't you mean 1991ABC!$L$268 ? -- David Biddulph "Flipper" wrote in message ... when I use it a written I get "REF" so something is not right. -- Allan "David Biddulph" wrote: No. You use the formula as given. .... or you could use =Indirect("1991ABC$L$26"&month(today())) instead of =Indirect("1991ABC$L$26"&month(now())) If there are functions which you don't understand, look them up in Excel help. now() returns current date and time today() returns current date month(...) returns the month number for the date given in the argument, so for a date in May, month(...) returns 5. -- David Biddulph "Flipper" wrote in message ... Sean I'm daft. I don't understand how to use indirect. At the point that you insert "month(now()))" what does that mean? Would I actually type in, for example, May 2009, and then now, and don't get it. Can you help me with that too. Thanks -- Allan "Sean Timmons" wrote: Oh! OK.. Then =Indirect("1991ABC$L$26"&month(now())) Would return the value in 1991ABC$L$265 so.. if you wanted 268 instead, just make it =Indirect("1991ABC$L$26"&month(now())+3) "Flipper" wrote: That won't work as the destination cell remains the same from month to month -- Allan "Sean Timmons" wrote: I presume your destination data is straight down the row. In that case, delete the $ prior to your row number, and the row number will automatically increase as you go down your rows. "Flipper" wrote: I produce a monthly report on investments where the ending balance of an account is reported in one column and the interest earned is reported in another column. The data for this report comes from another workbook, so each entry is linked to this other workbook. Since I produce this report monthly, I would like to be able to update those links automatically rather than manually. For example, in month 1, the link would be 1991ABC$L$268. In month 2 the link would need to be udpated to 1991ABC$L$269, month 3 1991ABC$L$270, etc. Thanks -- Allan |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Systematically updating references to other workbooks
I see one thing I missed. Won't work right in October - December.. do this
instead... =Indirect("1991ABC!$L$2"&60+month(today())) Other would have been 2610 instead of 270 for October.... "Flipper" wrote: That is correct, but I'm still getting and error. -- Allan "David Biddulph" wrote: Don't you need an exclamation mark after the sheet name in your reference? You were talking about 1991ABC$L$268 but didn't you mean 1991ABC!$L$268 ? -- David Biddulph "Flipper" wrote in message ... when I use it a written I get "REF" so something is not right. -- Allan "David Biddulph" wrote: No. You use the formula as given. .... or you could use =Indirect("1991ABC$L$26"&month(today())) instead of =Indirect("1991ABC$L$26"&month(now())) If there are functions which you don't understand, look them up in Excel help. now() returns current date and time today() returns current date month(...) returns the month number for the date given in the argument, so for a date in May, month(...) returns 5. -- David Biddulph "Flipper" wrote in message ... Sean I'm daft. I don't understand how to use indirect. At the point that you insert "month(now()))" what does that mean? Would I actually type in, for example, May 2009, and then now, and don't get it. Can you help me with that too. Thanks -- Allan "Sean Timmons" wrote: Oh! OK.. Then =Indirect("1991ABC$L$26"&month(now())) Would return the value in 1991ABC$L$265 so.. if you wanted 268 instead, just make it =Indirect("1991ABC$L$26"&month(now())+3) "Flipper" wrote: That won't work as the destination cell remains the same from month to month -- Allan "Sean Timmons" wrote: I presume your destination data is straight down the row. In that case, delete the $ prior to your row number, and the row number will automatically increase as you go down your rows. "Flipper" wrote: I produce a monthly report on investments where the ending balance of an account is reported in one column and the interest earned is reported in another column. The data for this report comes from another workbook, so each entry is linked to this other workbook. Since I produce this report monthly, I would like to be able to update those links automatically rather than manually. For example, in month 1, the link would be 1991ABC$L$268. In month 2 the link would need to be udpated to 1991ABC$L$269, month 3 1991ABC$L$270, etc. Thanks -- Allan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Updating Absolute References | Excel Worksheet Functions | |||
| How do I systematically do this? | Excel Discussion (Misc queries) | |||
| Updating Workbooks from multiple links Workbooks | Excel Worksheet Functions | |||
| Links/Cell References Not Updating | Excel Worksheet Functions | |||
| Prevent Chart from automatically updating references | Charts and Charting in Excel | |||
Linear Mode
