| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Auto fill of formula
I can not get my auto fill to work. I will give you example of my formula
=SUM('H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$2) -- I would like to drag this formuala down a column and have the Cell to increase by one for example: =SUM('H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$3) I don't know why it it notting adding the one. Thank you karenr |
|
#2
|
|||
|
|||
|
Hi
try: =SUM('H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B2,'H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B2,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B2,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B2) -- Regards Frank Kabel Frankfurt, Germany ksr wrote: I can not get my auto fill to work. I will give you example of my formula =SUM('H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$2) -- I would like to drag this formuala down a column and have the Cell to increase by one for example: =SUM('H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$3) I don't know why it it notting adding the one. Thank you karenr |
|
#3
|
|||
|
|||
|
Hi ksr
in the formula you are filling down you have absolute references $B$2 if you want the 2 to change to a 3 you need to remove the $ e.g. $B2 Cheers JulieD "ksr" wrote in message ... I can not get my auto fill to work. I will give you example of my formula =SUM('H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$2) -- I would like to drag this formuala down a column and have the Cell to increase by one for example: =SUM('H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$3) I don't know why it it notting adding the one. Thank you karenr |
|
#4
|
|||
|
|||
|
Yes that worked. IF I want the B to change to a C do I remove $ as well
"JulieD" wrote: Hi ksr in the formula you are filling down you have absolute references $B$2 if you want the 2 to change to a 3 you need to remove the $ e.g. $B2 Cheers JulieD "ksr" wrote in message ... I can not get my auto fill to work. I will give you example of my formula =SUM('H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$2) -- I would like to drag this formuala down a column and have the Cell to increase by one for example: =SUM('H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$3) I don't know why it it notting adding the one. Thank you karenr |
|
#5
|
|||
|
|||
|
ksr
Yes that worked. IF I want the B to change to a C do I remove $ as well If you are dragging across a row........Yes. If dragging down a column........the B won't change in spite of removing the $ from $B Gord Dibben Excel MVP On Wed, 15 Dec 2004 07:01:05 -0800, ksr wrote: "JulieD" wrote: Hi ksr in the formula you are filling down you have absolute references $B$2 if you want the 2 to change to a 3 you need to remove the $ e.g. $B2 Cheers JulieD "ksr" wrote in message ... I can not get my auto fill to work. I will give you example of my formula =SUM('H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$2) -- I would like to drag this formuala down a column and have the Cell to increase by one for example: =SUM('H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$3) I don't know why it it notting adding the one. Thank you karenr |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto fill of formula
Is there a way of tweaking Excel temporarily so columns increase as you drag
down? "Gord Dibben" wrote: ksr Yes that worked. IF I want the B to change to a C do I remove $ as well If you are dragging across a row........Yes. If dragging down a column........the B won't change in spite of removing the $ from $B Gord Dibben Excel MVP On Wed, 15 Dec 2004 07:01:05 -0800, ksr wrote: "JulieD" wrote: Hi ksr in the formula you are filling down you have absolute references $B$2 if you want the 2 to change to a 3 you need to remove the $ e.g. $B2 Cheers JulieD "ksr" wrote in message ... I can not get my auto fill to work. I will give you example of my formula =SUM('H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$2) -- I would like to drag this formuala down a column and have the Cell to increase by one for example: =SUM('H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$3) I don't know why it it notting adding the one. Thank you karenr |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto fill of formula
I suppose you could do wonderful things with a macro.
Why is it necessary to restore the formulas back to absolute references. Another possibility might be the use of OFFSET. using the row number to offset. =OFFSET(Sheet4$H$3,ROW()-1,0) -- --- 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 "Rothman" wrote in message ... Is there a way of tweaking Excel temporarily so columns increase as you drag down? "Gord Dibben" wrote: ksr Yes that worked. IF I want the B to change to a C do I remove $ as well If you are dragging across a row........Yes. If dragging down a column........the B won't change in spite of removing the $ from $B Gord Dibben Excel MVP On Wed, 15 Dec 2004 07:01:05 -0800, ksr wrote: "JulieD" wrote: Hi ksr in the formula you are filling down you have absolute references $B$2 if you want the 2 to change to a 3 you need to remove the $ e.g. $B2 Cheers JulieD "ksr" wrote in message ... I can not get my auto fill to work. I will give you example of my formula =SUM('H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$2) -- I would like to drag this formuala down a column and have the Cell to increase by one for example: =SUM('H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$3) I don't know why it it notting adding the one. Thank you karenr |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto fill of formula
Thanks for your help; OFFSET does the trick.
"David McRitchie" wrote: I suppose you could do wonderful things with a macro. Why is it necessary to restore the formulas back to absolute references. Another possibility might be the use of OFFSET. using the row number to offset. =OFFSET(Sheet4$H$3,ROW()-1,0) -- --- 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 "Rothman" wrote in message ... Is there a way of tweaking Excel temporarily so columns increase as you drag down? "Gord Dibben" wrote: ksr Yes that worked. IF I want the B to change to a C do I remove $ as well If you are dragging across a row........Yes. If dragging down a column........the B won't change in spite of removing the $ from $B Gord Dibben Excel MVP On Wed, 15 Dec 2004 07:01:05 -0800, ksr wrote: "JulieD" wrote: Hi ksr in the formula you are filling down you have absolute references $B$2 if you want the 2 to change to a 3 you need to remove the $ e.g. $B2 Cheers JulieD "ksr" wrote in message ... I can not get my auto fill to work. I will give you example of my formula =SUM('H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$2,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$2) -- I would like to drag this formuala down a column and have the Cell to increase by one for example: =SUM('H:\Sales Reports for 2005\[Mark Lacher Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Personal Lines Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'H:\Sales Reports for 2005\[Maura Derstein Sales Report 2005.xls]2005 YTD TOTALS'!$B$3,'[Mark Derstine Sales Report 2005.xls]2005 YTD TOTALS'!$B$3) I don't know why it it notting adding the one. Thank you karenr |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
| Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
| Auto fill box | Excel Discussion (Misc queries) | |||
| Auto Fill Options | Excel Discussion (Misc queries) | |||
| Formula Fill properties | Excel Worksheet Functions | |||
Linear Mode
