Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Text problems

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    NYC
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    1st. In cell G10, I have a to-from destinations. Eg LAX-SFO-DEN.
    I would like to have the “-“ indicate that cells I10:R10 should be added together and the total put in cell T10.

    2nd. At the bottom of a column where I have a total, I would like to have this sum automatically sent to the top of the next page, in the first cell, in the corresponding column. Should I do this on the same sheet or make another sheet?

    I am using Excell 2000
    I am trying to make a log book.
    I have tried every fx that I can think of

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no replies....

    I assume these are all Flight locations ?

    is all your conditional data the same 11 character format ?

    ie AAA-BBB-CCC

    and LHT-PAF-BOL

    or could some be direct flights ? : LAX-NYC

    and LHT-BOL ?

    for example....

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    NYC
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the response.

    That is correct. These are destinations and I may have AAA-BBB, AAA-BBB-CCC or even four destinations. To complicate matters, some data in a cell in the same column might not be a destination but read “Training”.

    I don’t know if you can help but I do appreciate the reply.

    Thanks Gord

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,649
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-27 09:22, GordNYC wrote:
    1st. In cell G10, I have a to-from destinations. Eg LAX-SFO-DEN.
    I would like to have the “-“ indicate that cells I10:R10 should be added together and the total put in cell T10.

    2nd. At the bottom of a column where I have a total, I would like to have this sum automatically sent to the top of the next page, in the first cell, in the corresponding column. Should I do this on the same sheet or make another sheet?

    I am using Excell 2000
    I am trying to make a log book.
    I have tried every fx that I can think of
    In T10 enter:

    =ISNUMBER(SEARCH("-",G10))*SUM(I10:R10)

    Is this what you're looking for?

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    2nd part :

    (assuming your original sheet was called "flights")

    in A1 on your new sheet, enter :

    =INDEX(flights!A:A,MATCH(9.99999999999999E+307,flights!A:A))

    and copy across as many columns as needed

    (I'm assuming you just meant a different sheet, as opposed to the sheet with your above "-" formula in T1)

    also, I borrowed this from Aladin's often-used solution, so he's technically answered both questions



    :: Pharma Z - Family drugstore ::

  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    NYC
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks to Chris and Aladin.
    Very nice of you to reply.
    The formula; =ISNUMBER(SEARCH("-",G10))*SUM(I10:R10) worked like a charm.
    I was trying to do it with a text function.

    The formula; =INDEX(flights!A:A,MATCH(9.99999999999999E+307,flights!A:A))
    I have not yet used. I don’t know if I am going to repeat these pages on the same sheet or use individual sheets for each page of log.

    Thanks again

    Gord

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    NYC
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have tried the formula, =INDEX(flights!A:A,MATCH(9.99999999999999E+307,flights!A:A))
    It does not seem to work. I may be using it incorrectly, but it is probably due to my poor description.

    On sheet One, my template, which I need to copy many times, contains columns G:W.
    In row 5 of these columns are the totals that are brought forward from the previous page (or sheet, I do not know which would be easier). The totals on the preceding page are in columns G:W, row 27.

    I could just copy and past the totals from G:W row 27 from the preceding page, onto the active page/sheet Into G:W row 5.
    It would take a great deal of time and I do not think it is the proper way of doing it.

    Any Ideas?

    Thank you again for your help.

    GordNYC

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,649
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On sheet One, my template, which I need to copy many times, contains columns G:W.
    In row 5 of these columns are the totals that are brought forward from the previous page (or sheet, I do not know which would be easier). The totals on the preceding page are in columns G:W, row 27.

    I could just copy and past the totals from G:W row 27 from the preceding page, onto the active page/sheet Into G:W row 5.
    It would take a great deal of time and I do not think it is the proper way of doing it.

    Any Ideas?


    In G5 of the "active page", that is, in the destination sheet enter:

    =INDEX(Source!$G$27:$W$27,COLUMN()-6)

    and copy across to W5 in the destination sheet. Substitute the actual name of the sheet for 'Source'.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-31 09:33 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •