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 moving last cell info to bottom row

Discussion in 'Business Applications' started by Photopilot, Dec 21, 2011.

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

    Photopilot Thread Starter

    Joined:
    Mar 27, 2007
    Messages:
    216
    I have created a spreadsheet to keep track of an aircrafts logs and cycles. I am able to crunch the numbers I need in some formulas. The problem I am having is as I add another trip I need to recalculate the formulas using the new occupied cells info. Is there a way to have the last cell in a rows information be moved down to a row at the bottom of the page so I can always have the last flights info in one spot to use in a formula?
     
  2. Pedro15

    Pedro15

    Joined:
    Oct 5, 2008
    Messages:
    386
    Would something like
    Code:
    =MAX(A1:A40)
    work.

    If not a copy of a mock up might help understand what you are after.

    Pedro
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    If you have values as follows:

    "1" in A1
    "2" in A2
    "3" in A3

    then =INDIRECT("A"&COUNTA(A:A)) in any column but A will give you the "bottomest" value in A. Provided A has no blanks. Will that do?
     
  4. Photopilot

    Photopilot Thread Starter

    Joined:
    Mar 27, 2007
    Messages:
    216
    Thanks guys
    The Max(A1:A10) worked not that I think it is getting the information from the lowest cell in the column but because all these calculations are sequential the lowest cell in the column has the greatest value in it and therefore brings the info I want into the cell I want.

    I was not able to get the "indirect" formula to work as I did not understand the wording in able to formulate it for my worksheet. I would be interested to see if you can plug it into my worksheet to see it coded properly so it works.

    The last thing I need to make this worksheet complete is to get some If-Then formulas working for me. Currently I add the formula or place some info in cells as I need to make the sheet work. For example when i enter an "X" into column G I also enter the hobbs reading from column E into column H. I would appreciate it if someone would help me with an If-then formula that would get this to happen with me only needing to enter the "X" into column G. I need H13 to read (if G13=x then H13=E13).
     

    Attached Files:

  5. Photopilot

    Photopilot Thread Starter

    Joined:
    Mar 27, 2007
    Messages:
    216
    Never mind I went and did a little research after asking the question. What I have now works exactly as needed. Attached is my latest version. The one thing I would hope to improve though to make the worksheet a little more presentable, is to not have a zero in the case of the "If formulas" is not met.

    What I got going now is that if I have made a run in the plane therefore, the ending hobbs is entered and column F has an entry If(F5>0, then add the appropriate amount to each of the other columns. If not then it enters a zero in the column. Is there a way to leave the column blank if no run has been made and therefore no entry need be made?
     

    Attached Files:

  6. Photopilot

    Photopilot Thread Starter

    Joined:
    Mar 27, 2007
    Messages:
    216
    Once again I was able to solve it myself

    =IF(F6>0,K5+1,"")

    When looking to leave the formula blank as a result just use two sets of quotation marks. Essentially putting nothing in a set of quotation marks, will post nothing in the cell. ""
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    =indirect("f"&match(max(b:b),b:b))

    (f26)
     
  8. Pedro15

    Pedro15

    Joined:
    Oct 5, 2008
    Messages:
    386
    Highlight E5:K23 and then go Tools/options/View Untick "zero values"
     
  9. 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/1032228

  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