EXCEL 3

[transparent]

I have the following formula on each row

=SUM(N3,E3,G3,I3,K3)

Is there a more gneric way of creating this formular so that it can be applied to the how column, so that I would get the equiv of:

=SUM(N2,E2,G2,I2,K2)
=SUM(N3,E3,G3,I3,K3)
=SUM(N4,E4,G4,I4,K4)


etc??

 

[xlbo]

have you even bothered to look at the help file ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[transparent]

I have looked - and I cant find the solution, otherwise I wouldnt be asking

 

[xlbo]

really - have you searched for "SUM" ??

What do you not understand about examples 3 & 4 ?? (XP but I know they are there in other versions as well)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

failing that, try searching on "reference" and have a look at "switching between reference styles" or similar

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[transparent]

I know about the fill handle method of doing this i.e. selecting the cell and double clicking the "Fill Handle", however, this requires that the user of the spreadsheet also knows about this functionality...

 

[xlbo]

better tell them then.....either that or they can copy and paste the formula - I really don't see the point of this question - what is it that you are trying to achieve here ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DPlank]

You mean someone other than you is going to be creating formulas in your spreadsheet and you're not sure if they know about this functionality?

Or selecting your area of cells and pressing CTRL-D to fill down?

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[transparent]

This is actually the first time I've ever used Excel, so I'm as much a novice as the person who will be using it.

My aim is automate things as much as possible. I was hoping to apply the formula to the whole column in the same way you might with column validation.

I dont want to rely on the user knowing about this level of functionality in Excel.

It is unlikely that the user will ever create formulas themselves, however, they will be adding additional rows of data.

They will expect the columns to automatically update - they probably wont know about ctrl-d etc

 

[xlbo]

coupla options then (although I don't understand why you can't just tell your users)

1: Copy the formula all the way down - this will have 2 side effects:
a: spreadsheet size will increase a lot
b: users will see zeros where there is no data

2: Wrap the formula in an IF statement to prevent the zeroes - this will still create a bigger file size though

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[transparent]

Cheers.

 

[AnotherHiggins]

What version of Excel are you running? I know this will work in 2000 and XP - not sure about earlier versions:

Go to Excel's help file to the Answer Wizard and look up
Fill in a series from a formula

This is a method that will allow Excel to automatically fill in formulas when you enter new rows of data.

To accomplish this, you have to start with the formula copied down in at least four contiguous rows. If you are going to start the user off with an 'empty' sheet, then use an IF statement like
=if(b1="","",a1+b1)
to hide the zeros resulting from formulas on blank lines.

Then fill that formula down for four rows.

Now the formula will populate itself as you work your way down the sheet!

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[MeGustaXL]

I never knew about

Fill in a series from a formula

Excellent Tip from anotherhiggins ==>*

Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[AnotherHiggins]

Thanks, Chris. This is a great method* since it avoids the 'bigger file size' mentioned in Geoff's post.

transparent: I realize this is a bit late, but I hope you take a look at my last post as it does what you want quite nicely.

*I had observed Excel doing this for me on several occasions, but only recently took the time to investigate how it works.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.