+ Reply to Thread
Results 1 to 11 of 11

=INDIRECT formula

  1. #1
    Registered User
    Join Date
    03-26-2013
    Location
    nottingham
    MS-Off Ver
    Excel 2007
    Posts
    18

    =INDIRECT formula

    Hi,
    I currently have formula
    =INDIRECT("'data 24'!A"&(COUNTA('DATA 24'!$A:$A)-(ROW($A$14)-ROW($A4)))+2)

    this looks at sheet 'data 24' and picks up data from column A the 12th from last entry.

    However I have a number of entries that are '0' I would like to ignore these and pick up the 12th last 'text' entry,
    how can I amend the formula to look only for text, rather than numbers??

    Any help greatly appreciated
    Many thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: =INDIRECT formula

    try using a helper column that tests for text, and use that for the test?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-26-2013
    Location
    nottingham
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: =INDIRECT formula

    thanks, how would I do this? - thanks

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: =INDIRECT formula

    not knowing what your data looks like, you will need to adjust this, but something like...

    =if(istext(A1),A1,"")

  5. #5
    Registered User
    Join Date
    03-26-2013
    Location
    nottingham
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: =INDIRECT formula

    so would this helper column need to be in the column with the current formula in, or in the one it's looking at 'data 24' ?
    thanks

  6. #6
    Registered User
    Join Date
    03-26-2013
    Location
    nottingham
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: =INDIRECT formula

    To make it clearer, in tab 'data 24' I have following data:
    Column A = team name
    i.e. Arsenal but also sometimes '0'
    formula needs to pick up the 12th last 'text cell' ignoring the '0' values

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: =INDIRECT formula

    upload a sample workbook so I can see what you are working with
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  8. #8
    Registered User
    Join Date
    03-26-2013
    Location
    nottingham
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: =INDIRECT formula

    file too large, only 2.9mb?

  9. #9
    Registered User
    Join Date
    03-26-2013
    Location
    nottingham
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: =INDIRECT formula

    to be honest this was a bit of a workaround as nobody responded to my earlier thread regarding MS Query altering my array formulas?
    perhaps you'd be kind enough to take a look at that for me and advise?
    Many thanks

  10. #10
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: =INDIRECT formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    array entered should do it
    OR
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by martindwilson; 04-03-2013 at 05:22 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  11. #11
    Registered User
    Join Date
    03-26-2013
    Location
    nottingham
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: =INDIRECT formula

    Hi FDIBBINS, thanks for your help thus far. just wondered if you might get chance to look at my other thread on MS Query altering my array formula's?
    would be greatly appreciated - many thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1