# Excel - easy question!??!

Discussion in 'Business Applications' started by Bigd519, Jan 14, 2003.

Not open for further replies.

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.

Thanks,
David

2. ### itsmeitsmeitsme

Joined:
Apr 29, 2002
Messages:
789
so the column you will add do you want it to be in the formula also???is that my understanding?

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.

-David

4. ### itsmeitsmeitsme

Joined:
Apr 29, 2002
Messages:
789
what happens to the data in the what was column k? it will no longer be involved in the calculation???

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

6. ### itsmeitsmeitsme

Joined:
Apr 29, 2002
Messages:
789
Maybe someone else will know but from what i have researched you cant get there from here>>>>>>>

7. ### bassetmanModerator (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.

8. ### RandyG

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

9. ### itsmeitsmeitsme

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....

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.

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

12. ### RandyG

Joined:
Jun 26, 2000
Messages:
7,762
No probs folks!

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"))

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

14. ### RandyG

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!! *** I did a graph the old way, and now I remember why I use Pivot Graphs now!!***

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.

15. ### RandyG

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

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.

As Seen On