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.

Solved: Excel query please :)

Discussion in 'Business Applications' started by Jo1lygrngian, Jan 1, 2006.

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

    Jo1lygrngian Thread Starter

    Joined:
    Nov 3, 2005
    Messages:
    87
    Hi there, im getting really stuck with this excel formula, and wondering if any bright sparks out there can help an aging man lol

    right here goes.. I'm working on a spreadsheet, for my accounts, on a week to week basis.

    There is a final page, which will store all the calculations from wk1 to wk52. When i try and copy the values from week to week, it always adds a cell below..

    example being, I want to copy page1, cell b13, page2 cell b13, page 3 cell b13, onwards.. paste it into the final page, and it comes out with

    =+'wk 8'!B25, then the cell below, is =+'wk8'!B26, so on, and so on.

    Is there any way at all, of making it take the initial cell and then not the one below in the calculation??

    I've probably explained this all wrong, so if you need any more clues, plse dont hesitate to shout, and ill try and explain it better.

    Btw, if it involves a macro, then im WELL stuck, as i dont know where to start.. and would really appreciate a step by step help on writing one.

    (Bring back lotus!!) lol

    thanks for your time, and any help you can give, and happy new year to one and all

    Be safe

    Nige :)
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I don't get "it always adds a cell below". If you mean "the row number increments", you could try using an absolute reference in your formula to anchor it, such as:

    ='wk 1'!$B$13

    However, that won't make the sheet number increment.

    You can use a formula on the last sheet to sum (across) all the other sheets, such as:

    =SUM('wk 1:wk 52'!B13)

    , that might come in useful.

    Any chance you could upload a sample file to give a clearer idea? :confused:

    Rgds,
    Andy
     
  3. seamanken

    seamanken

    Joined:
    Dec 30, 2005
    Messages:
    9
    Here's the quickest way to do that goto the summary page you have setup click the cell were your totals will be. got to sheet 1 hold down the shift key and click the cell you want it will add it for you the goto the next sheet so on and so on. remember though you can only have 30 0f these per cell so I break mine down into quaters and have the quaters added on the same summary page
     
  4. seamanken

    seamanken

    Joined:
    Dec 30, 2005
    Messages:
    9
    I am not sure sending you a file would help it has to be done on the book your working on
     
  5. Jo1lygrngian

    Jo1lygrngian Thread Starter

    Joined:
    Nov 3, 2005
    Messages:
    87
    Hi guys, and thanks for getting back to me, i really do appreciate it.

    thanks so much for getting back to me on the thread. I have uploaded the ss for you, so that you can see exactly what i mean by 'it always adds the cell below'.

    A good example being, on page Gross takings summ, I want it to take the total of wk1 b13, and place it in cell b4, then take the total of wk2 b13, put that in b5, etc. I have tried using the =SUM('wk 1:wk 52'!B13) formula, and it comes up with #ref. Using the 'shift and click, sorry to say has made little difference. :(

    I would really appreciate any help you can give here, as it is really starting to do my head in!!

    Hope you have a grt day

    be safe nige :)
     

    Attached Files:

  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    So it's just a summary? For starters, try this in B2 of Sheet2 and copy it down:

    =INDIRECT("wk"&$A2&"!B13")
     
  7. Jo1lygrngian

    Jo1lygrngian Thread Starter

    Joined:
    Nov 3, 2005
    Messages:
    87
    Andy.. THANK YOU!!!

    It works in the sml sheet a treat. Ive copied it into the main sheet, and it works from week 16-52, but for some reason not wks1-15 :rolleyes: Ill have a play around, to see what the cause of this is.

    Long ago are the days when i used SS's on a reg basis, ahem, lotus123 anyone?? lol

    I simply cannot thank you enough.

    Hope you have a grt day

    thanks once again nige :)
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    You got the REF error before because (a) your sheet names don't actually have spaces in (b) there was no wk52 sheet.

    I still don't fully understand what you're trying to get from where -- e.g. there's no actual "page Gross takings". But by all means tinker with it & post back if you get stuck, or can provide more info. There are always people here able to help with Excel stuff.

    I think I had Lotus once, got it free on a cover disk ... says it all, probably. :D
     
  9. Jo1lygrngian

    Jo1lygrngian Thread Starter

    Joined:
    Nov 3, 2005
    Messages:
    87
    Thanks for the input Andy. It is now working perfectly
     
  10. 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/430095

  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