Excel - easy question!??!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Bigd519

Thread Starter
Joined
Jan 9, 2003
Messages
12
Hi everyone - I would love your help!

Im using excel, and have a formula that computes a sum of 6 columns: =SUM(E2:K2) . That's easy.

However, I need to add a column that will become E. Now my formula says: =SUM(F2:L2) because the formula refers to the original cells.

Its not a problem of absolute addressing, I tried that. I need a way for the cells to be fixed if I add a column.

Please help!

Thanks,
David
 

Bigd519

Thread Starter
Joined
Jan 9, 2003
Messages
12
Yes, I want the new column to be in the formula - I want the formula to always stay the same: =SUM(E2:K2) no matter if i add a column or two.
Right now adding a column changes the formula.

Thanks for your help!
-David
 

Bigd519

Thread Starter
Joined
Jan 9, 2003
Messages
12
Right.....I only want my formula to compute the sum of 5 columns, then when i insert a column the formula still computes columns e to k, but now the data that was in column k is now in column L, but not used in the formula any more.

-Dave
 

bassetman

Moderator (deceased) - Gone but never forgotten
Joined
Jun 7, 2001
Messages
47,973
I rarely use Excel, but in the old days I thought we could just enter something to the effect of L=K and it would display the value of K???

Like I said been a looong time.
 
Joined
Jun 26, 2000
Messages
7,762
=SUM(INDIRECT("E2:K2")) will work, the only problem you will have is that it stays that way no matter what cell you copy it into, meaning that in row 3, it will still refer to row 2. This means changing the formula manually for each row you have it in.


However, I believe this will do it for ya, after a once off fixing of row numbers in the formula for each cell you enter into it

=SUM(INDIRECT("E1:K1"))
=SUM(INDIRECT("E2:K2"))
=SUM(INDIRECT("E3:K3"))
=SUM(INDIRECT("E4:K4"))
=SUM(INDIRECT("E5:K5"))
etc
 
Joined
Apr 29, 2002
Messages
789
well thanks randy............i learned something again here, although I didnt ask the question I gave it a try and it works great......I have noticed you do know your excel.....thanks for the lesson....
 

Bigd519

Thread Starter
Joined
Jan 9, 2003
Messages
12
Thanks. So much.

It worked like a charm. I only had 10 or so rows, so I had to manually change the column numbers, but it was worth it!
Thanks again.
 

Bigd519

Thread Starter
Joined
Jan 9, 2003
Messages
12
Randy - Thanks so much again

A related question though -

Im trying to graph those columns now, and of course when I insert the new column, the same thing happens to the graph as happenned to the file before - it stays with the old columns, while i want it to drop the last column and use the new one.

Here is the code from the chart: =Sites!$E$2:$K$2

Could I use that INDIRECT code somewhere here?

Thanks-
David
 
Joined
Jun 26, 2000
Messages
7,762
No probs folks!:D


itsmeitsmeitsme - It's not so much that I know so much, but rather I know how to search so well!!;)

David - I believe you can use it there. Your formula would be =INDIRECT("Sites!E2:K2") but I don't see what you are trying to accomplish, as this just does a Count of the cells noted.

Are you trying for a sum of those? If so then it would be =sum(INDIRECT("Sites!E2:K2"))
 

Bigd519

Thread Starter
Joined
Jan 9, 2003
Messages
12
Randy - I only want the sum in the spreadsheet - I want the tally of each column in the grahph. So, thanks for your help!

-Dave
 
Joined
Jun 26, 2000
Messages
7,762
I use PivotGraphs, which takes a lot of the guesswork out of making graphs, so I am unsure of how you would use that. Shows how much I know, huh!!:D *** I did a graph the old way, and now I remember why I use Pivot Graphs now!!:D;)***

I did try the formula in a standard graph,and now see that it does not work in the Range section. Sorry

Question for you - what are you trying to do with all of this? I ask, because there may be another way to do it, then the one you are using.

If you would like, attach an example of the worksheet you are using, and I will see if I can rework it to do what you want it to do.

I still can't see the reason why you would insert a column, and still only want to measure the first 7! I would think replacing the data would be the route to go, as you are obviously not using the obsolete row of data anymore. Maybe after seeing your spreadsheet, I can understand it more fully, and thereby offer a better solution.

If you want to go this route, then attach the spreadsheet here, after renaming it with a txt extension. Or upload it somewhere else and provide a link to it. Last case scenario, PM me with your email address, and I will send you mine, so you can email the doc to me.
 
Joined
Jun 26, 2000
Messages
7,762
Hmmm, after receiving an example of the spreadsheet BigD wants, I am working away at it, but I did come up with a better way of using the indirect and sum formula that I have above

=SUM(INDIRECT(ADDRESS((ROW()),5)):INDIRECT(ADDRESS((ROW()),11)))


It looks a lot worse than it is. It is still the same thing as =SUM(INDIRECT("E1:K1")) except more versatile. Instead of specifying the row I wnat, I specify the column numbers (5-11 which corresponds to E-K) and the row() statement just calls the row that the formula is currently in. So, if I put that particular code in cell D2, as per BigD's example he sent, then I would be adding columns 5, 6, 7, 8, 9, 10, 11 together, which would be E, F, G, H, I, J, and K. Inserting a new column would never change this, and now you would not have to manually change the cell reference in each line. The code I put above would be placed in every cell that used to contain =SUM(INDIRECT("E1:K1")) or one of it's variants.


the problem I am having is to do the chart!!

I ended up using the Source for the graph as totally different columns, which I pulled the latest info from. End of the day is that I believe it will do everything that BigD wants, but I'll reserve judgement till he gets back to me, or us.

Anyway, I have sent what I have done back to BigD, and we'll wait to see what he comes back with.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top