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: Show values of non-blank cell

Discussion in 'Business Applications' started by news, Dec 25, 2012.

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

    news Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    20
    Is there a formula where it will show the text within a range of data?:)
    For example "a1" will show 1-10, "a2" will show 21-30.
    columns
    a b c d e f g
    Row1 0 1-10 0 0 0 0
    Row2 0 0 21-30 0 0 0
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,421
    First Name:
    Wayne
    is the text only in one cell in the row and what about numbers

    row 1 = 9 , 12, abc , 1, 0,0,0,0, 1-10

    do you want to show just abc and 1-10
     
  3. news

    news Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    20
    Only "1-10" is required to show. a b c d e f g is column in the illustrations
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,421
    First Name:
    Wayne
    but would the other characters exist at all

    you could use conditional format to highlight the cell with text
    use
    istext()
    in a conditional format

    see example spreadsheet
     

    Attached Files:

  5. news

    news Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    20
    Refer to your attachment as illustration of what I need.

    For cell G3, it will scan through A3 to E3 to capture & display "abc". What formula will allow G3 to have such function?

    For my case, G3 will be displaying 1-10 or 11-20 or 21-30 and so on. Only one cell between A3 to E3 will have such 'value'.

    Hope above is clearly illustrated.
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,421
    First Name:
    Wayne
    does this work
    =LOOKUP("istext",A3:E3)

    only one TEXT value ?
     
  7. news

    news Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    20
    Yes, I will try it. Thanks.
     
  8. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    =INDEX(A3:E3,1,MIN(IF(A3:E3>0,COLUMN(A3:E3),"a")))

    Make sure to enter it with ctrl+shft+entr, cos it's an array formula
     
  9. news

    news Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    20
    Hi etaf,
    Your solution don't work..

    Hi Garf13LD,
    You solution don't work too...

    Can anyone share with me how to attach the excel so that you can view the data & come out with the best solution?
     
  10. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,421
    First Name:
    Wayne
    To upload to the forum, open the full reply window ("Go Advanced" button) and use the Manage Attachments button to upload it here.
    Full details are available here http://library.techguy.org/wiki/TSG_Posting_a_Screenshot

    make sure its dummy data - as you are on a public forum and google will index
     
  11. news

    news Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    20
    Hi,

    Please refer to the attachment, column B.

    I have input a formula from B2 to B8, it works. However, the formula doesn't work from B9 downwards.
    Any solution?

    Because I'm not so good in formula, I input data from K to T.

    The concept is if the number in Column A2 (downwards) is 1, B2 will show "1-10"; if 12, B2 will show "11-20" and so on.
     

    Attached Files:

  12. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,421
    First Name:
    Wayne
    sorry, I still do not fully understand what you are trying to do

    BUT in the spreadsheet - K to T is all text as you have entered "0" so thats text and not a number - which is why the formulas do not work

    if you change the formula from "0" to just 0
    then the lookup works - see attached file

    Not sure what you really want
     

    Attached Files:

  13. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    B2=INDEX(K2:T2,1,MIN(IF(LEN(K2:T2)>1,COLUMN(K2:T2)-COLUMN(J2),"a")))
     
  14. news

    news Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    20
    Hi etaf,
    It really works..... thanks.....

    Hi Garf13LD,
    I tried your formula and it return "#value!"....
     
  15. 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/1082300

  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