ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Auto fill of formula (https://www.excelbanter.com/excel-worksheet-functions/7946-auto-fill-formula.html)

ksr

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

Frank Kabel

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




JulieD

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




ksr

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





Gord Dibben

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






Rothman

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






David McRitchie

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








Rothman

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










All times are GMT +1. The time now is 08:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com