1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - easy question!??!

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

Thread Status:
Not open for further replies.
Advertisement
  1. Bigd519

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

    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?
     
  3. Bigd519

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

    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???
     
  5. Bigd519

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

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

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

    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

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

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

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

    RandyG

    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"))
     
  13. Bigd519

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

    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!!: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.
     
  15. RandyG

    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

    =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.
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/113229

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice