Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extend formats and formulas
Does anyone know why a running sum type of formula =SUM(B2,C1) does not
automatically copy down? Similarly, I cannot get a fill colour to extend down when a date format is used. Formats for numbers and text values copy down OK. It is just dates and the running sum type of formula that do not seem to work. I have followed all suggestions for extended formats and formulas in the Knowledge Base. PS Excel 2000 Vs 9.0.3821 SR-1 Many thanks Robert |
#2
|
|||
|
|||
Change the formula to =B2+C1. There is no need for the SUM part if you're
only adding two numbers. You're just unnecessarily adding "weight" to your file. ************ Anne Troy www.OfficeArticles.com "JRB" wrote in message ... Does anyone know why a running sum type of formula =SUM(B2,C1) does not automatically copy down? Similarly, I cannot get a fill colour to extend down when a date format is used. Formats for numbers and text values copy down OK. It is just dates and the running sum type of formula that do not seem to work. I have followed all suggestions for extended formats and formulas in the Knowledge Base. PS Excel 2000 Vs 9.0.3821 SR-1 Many thanks Robert |
#3
|
|||
|
|||
Thanks Anne
I tried your suggestion. The formula still does not 'extend' down to the new row "Anne Troy" wrote: Change the formula to =B2+C1. There is no need for the SUM part if you're only adding two numbers. You're just unnecessarily adding "weight" to your file. ************ Anne Troy www.OfficeArticles.com "JRB" wrote in message ... Does anyone know why a running sum type of formula =SUM(B2,C1) does not automatically copy down? Similarly, I cannot get a fill colour to extend down when a date format is used. Formats for numbers and text values copy down OK. It is just dates and the running sum type of formula that do not seem to work. I have followed all suggestions for extended formats and formulas in the Knowledge Base. PS Excel 2000 Vs 9.0.3821 SR-1 Many thanks Robert |
#4
|
|||
|
|||
Okay, let's define "extend", then. Or perhaps this article (the bit about
the Fill Handle) will help: http://www.officearticles.com/excel/...soft_excel.htm ************ Anne Troy www.OfficeArticles.com "JRB" wrote in message ... Thanks Anne I tried your suggestion. The formula still does not 'extend' down to the new row "Anne Troy" wrote: Change the formula to =B2+C1. There is no need for the SUM part if you're only adding two numbers. You're just unnecessarily adding "weight" to your file. ************ Anne Troy www.OfficeArticles.com "JRB" wrote in message ... Does anyone know why a running sum type of formula =SUM(B2,C1) does not automatically copy down? Similarly, I cannot get a fill colour to extend down when a date format is used. Formats for numbers and text values copy down OK. It is just dates and the running sum type of formula that do not seem to work. I have followed all suggestions for extended formats and formulas in the Knowledge Base. PS Excel 2000 Vs 9.0.3821 SR-1 Many thanks Robert |
#5
|
|||
|
|||
Hi Robert,
You have to have 3 rows immediately above with the same formatting, bank statement with a debit or a credit filled in, but not both would not be the same format. You would have to be lower down on the sheet for the fill formulas and formatting to work. I would suggest that for a formula on row 2 that =B2 + OFFSET(C2,-1,0) would work better if you want to rearrange your rows, or insert or delete rows to prevent you from getting a #REF! error. Normally you would have column headers (labels) at the top of each column to indicate what is in the column. see http://www.mvps.org/dmcritchie/excel/insrtrow.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JRB" wrote in message ... Does anyone know why a running sum type of formula =SUM(B2,C1) does not automatically copy down? Similarly, I cannot get a fill colour to extend down when a date format is used. Formats for numbers and text values copy down OK. It is just dates and the running sum type of formula that do not seem to work. I have followed all suggestions for extended formats and formulas in the Knowledge Base. PS Excel 2000 Vs 9.0.3821 SR-1 Many thanks Robert |
#6
|
|||
|
|||
David thnks for your reply. I have many rows above the new row that have
identical data types and formatting. I tried the formula you sugest and it produces the running sum that I require, but this formula also does not copy down when I fill in the rest of the new row at the bottom of the list. I have found that a formula that references only cells in the same row as the formula copy down successfully. If the formula references a cell in the row above as is needed by a running sum, then the extending down of formulas does not seem to work. "David McRitchie" wrote: Hi Robert, You have to have 3 rows immediately above with the same formatting, bank statement with a debit or a credit filled in, but not both would not be the same format. You would have to be lower down on the sheet for the fill formulas and formatting to work. I would suggest that for a formula on row 2 that =B2 + OFFSET(C2,-1,0) would work better if you want to rearrange your rows, or insert or delete rows to prevent you from getting a #REF! error. Normally you would have column headers (labels) at the top of each column to indicate what is in the column. see http://www.mvps.org/dmcritchie/excel/insrtrow.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JRB" wrote in message ... Does anyone know why a running sum type of formula =SUM(B2,C1) does not automatically copy down? Similarly, I cannot get a fill colour to extend down when a date format is used. Formats for numbers and text values copy down OK. It is just dates and the running sum type of formula that do not seem to work. I have followed all suggestions for extended formats and formulas in the Knowledge Base. PS Excel 2000 Vs 9.0.3821 SR-1 Many thanks Robert |
#7
|
|||
|
|||
Thanks Anne, but I am referring to the 'Extend Formats and Formulas' that
applies to a list having consistant data, formatting and formulas in the rows above. Simply want a basic running sum formula to copy down automatically when I fill in a new row at the bottom of the list. "Anne Troy" wrote: Okay, let's define "extend", then. Or perhaps this article (the bit about the Fill Handle) will help: http://www.officearticles.com/excel/...soft_excel.htm ************ Anne Troy www.OfficeArticles.com "JRB" wrote in message ... Thanks Anne I tried your suggestion. The formula still does not 'extend' down to the new row "Anne Troy" wrote: Change the formula to =B2+C1. There is no need for the SUM part if you're only adding two numbers. You're just unnecessarily adding "weight" to your file. ************ Anne Troy www.OfficeArticles.com "JRB" wrote in message ... Does anyone know why a running sum type of formula =SUM(B2,C1) does not automatically copy down? Similarly, I cannot get a fill colour to extend down when a date format is used. Formats for numbers and text values copy down OK. It is just dates and the running sum type of formula that do not seem to work. I have followed all suggestions for extended formats and formulas in the Knowledge Base. PS Excel 2000 Vs 9.0.3821 SR-1 Many thanks Robert |
#8
|
|||
|
|||
Yes, I find that extend formulas and formats is unreliable and of little
use, but I do keep it turned on. If you look at the webpage, I referred you to you will find other solutions. such as invoking a macro to insert rows. An event macro to fill in the formatting and formulas once you enter something into a column. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JRB" wrote in message ... David thnks for your reply. I have many rows above the new row that have identical data types and formatting. I tried the formula you sugest and it produces the running sum that I require, but this formula also does not copy down when I fill in the rest of the new row at the bottom of the list. I have found that a formula that references only cells in the same row as the formula copy down successfully. If the formula references a cell in the row above as is needed by a running sum, then the extending down of formulas does not seem to work. "David McRitchie" wrote: Hi Robert, You have to have 3 rows immediately above with the same formatting, bank statement with a debit or a credit filled in, but not both would not be the same format. You would have to be lower down on the sheet for the fill formulas and formatting to work. I would suggest that for a formula on row 2 that =B2 + OFFSET(C2,-1,0) would work better if you want to rearrange your rows, or insert or delete rows to prevent you from getting a #REF! error. Normally you would have column headers (labels) at the top of each column to indicate what is in the column. see http://www.mvps.org/dmcritchie/excel/insrtrow.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JRB" wrote in message ... Does anyone know why a running sum type of formula =SUM(B2,C1) does not automatically copy down? Similarly, I cannot get a fill colour to extend down when a date format is used. Formats for numbers and text values copy down OK. It is just dates and the running sum type of formula that do not seem to work. I have followed all suggestions for extended formats and formulas in the Knowledge Base. PS Excel 2000 Vs 9.0.3821 SR-1 Many thanks Robert |
#9
|
|||
|
|||
Thanks David, will give the macro route a try. Pity extend formulas does not
work as advertised, it seems a pretty useful idea. Robert "David McRitchie" wrote: Yes, I find that extend formulas and formats is unreliable and of little use, but I do keep it turned on. If you look at the webpage, I referred you to you will find other solutions. such as invoking a macro to insert rows. An event macro to fill in the formatting and formulas once you enter something into a column. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JRB" wrote in message ... David thnks for your reply. I have many rows above the new row that have identical data types and formatting. I tried the formula you sugest and it produces the running sum that I require, but this formula also does not copy down when I fill in the rest of the new row at the bottom of the list. I have found that a formula that references only cells in the same row as the formula copy down successfully. If the formula references a cell in the row above as is needed by a running sum, then the extending down of formulas does not seem to work. "David McRitchie" wrote: Hi Robert, You have to have 3 rows immediately above with the same formatting, bank statement with a debit or a credit filled in, but not both would not be the same format. You would have to be lower down on the sheet for the fill formulas and formatting to work. I would suggest that for a formula on row 2 that =B2 + OFFSET(C2,-1,0) would work better if you want to rearrange your rows, or insert or delete rows to prevent you from getting a #REF! error. Normally you would have column headers (labels) at the top of each column to indicate what is in the column. see http://www.mvps.org/dmcritchie/excel/insrtrow.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JRB" wrote in message ... Does anyone know why a running sum type of formula =SUM(B2,C1) does not automatically copy down? Similarly, I cannot get a fill colour to extend down when a date format is used. Formats for numbers and text values copy down OK. It is just dates and the running sum type of formula that do not seem to work. I have followed all suggestions for extended formats and formulas in the Knowledge Base. PS Excel 2000 Vs 9.0.3821 SR-1 Many thanks Robert |
#10
|
|||
|
|||
Hi Robert,
Extend formulas and formats works as advertised it's just that are so many little things that can break the chain, like empty spots (cells) on a row. "JRB" wrote in message... Thanks David, will give the macro route a try. Pity extend formulas does not work as advertised, it seems a pretty useful idea. Robert |
#11
|
|||
|
|||
Hi David,
I have used the simplest of lists and formulas to test this. With my version of Excel, a formula that references cells in the Same row ALWAYS copies down. A formula that references a cell in any other row NEVER copies down. Formats (eg fill colour) in a date column never copies down. Perhaps there is a patch that fixes this for Excel 2000? Rgds Robert "David McRitchie" wrote: Hi Robert, Extend formulas and formats works as advertised it's just that are so many little things that can break the chain, like empty spots (cells) on a row. "JRB" wrote in message... Thanks David, will give the macro route a try. Pity extend formulas does not work as advertised, it seems a pretty useful idea. Robert |
#12
|
|||
|
|||
Hi, I have just trying to figure out the extend formats and formulas feature for a family member. The feature will not extend a formula that has been copy/pasted into the row. To avoid having to type a formula in each time you need to use Fill/Down instead of copy paste. I also have found that if the formula uses more than 2 columns you need 5 consectutive rows in order to get the sum to automatically appear. I also was able to successfully get a simple formua like =G2-G1 to automatically extend by using the previous tip of =G2-Offset(G2,-1,0). In addition if the formula has even the simplest of differences such as number formatting or even =sum(A1:G1) vs. =+sum(A1:G1) the extend won't work (until there are 5 consecutive rows with the same formula). Hope this helps you. Jennifer -- marisky23 ------------------------------------------------------------------------ marisky23's Profile: http://www.excelforum.com/member.php...o&userid=26736 View this thread: http://www.excelforum.com/showthread...hreadid=397774 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Formats | Excel Discussion (Misc queries) | |||
How to have formulas and formats auto copy to new inserted rows | Excel Discussion (Misc queries) | |||
Cell Formats in formulas | Excel Discussion (Misc queries) | |||
extend data range formats and formulas | Setting up and Configuration of Excel | |||
URGENT Please... new worksheet with copied formats but no data. | Excel Worksheet Functions |