+ Reply to Thread
Results 1 to 10 of 10

Display text based on last data row

  1. #1
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2010 & 2016 (NOT PT or PQ)
    Posts
    1,049

    Display text based on last data row

    hi expert...

    i am looking for excel formula or trick, how to make display certainty text dynamically based on last data ...

    for detail information, please see my workbook attached...

    any help, will be appreciated ...(please, not macro)..

    john m
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Display text based on last data row

    H John,

    Unfortunately, the data you provided doesn't support your request.

    1. You can't have "Robert Thang" appear if doesn't exist anywhere on the spreadsheet.
    2. # 3564 is also nowhere to be found.

    I order for us wright a formula we need to know where this data exists on the spreadsheet.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    03-22-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007,2010
    Posts
    39

    Re: Display text based on last data row

    HI Jhon Mustofa ,

    If there is no blank on column B, try this formula

    =+INDEX(B:B,COUNTA(B:B))

    Regards

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    Re: Display text based on last data row

    Okay, so you're going to be manually entering data (or pasting) into A,B and C and then one row past the last set of data you want your "sincerely" phrase?

    The only way I can think to do this without macros would be to prepopulate Column C with a formula that you would be writing over.
    In B7 copied down
    =IF(OR(B6={"","#3564"}),"",IF(B6="Robert Thang", "#3564",IF(B6="Sincerely,","Robert Thang", IF(ISTEXT(B6), "Sincerely,",""))))
    See attachment
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2010 & 2016 (NOT PT or PQ)
    Posts
    1,049

    Re: Display text based on last data row

    thank guys..

    i am confused for explain it but here new sample upload for illustration what i want it...

    i want text "Sincerely,Robert Thang,#3564" always display based on selected cell in K3(drop list selected)/adjustable,like text "total" (dynamic display)
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2010 & 2016 (NOT PT or PQ)
    Posts
    1,049

    Re: Display text based on last data row

    sorry, text ""Sincerely,Robert Thang,#3564" not in single row (see # 1 OP)

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    Re: Display text based on last data row

    IN your example, with "e" chosen in K3, what cells do you want "Sincerely, Robert Thang #3564" in?

  8. #8
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2010 & 2016 (NOT PT or PQ)
    Posts
    1,049

    Re: Display text based on last data row

    hi ChemistB, "Sincerely" in Column K, and 3 cells under "Sincerely" then "Robert Thang" 1 cell under "Robert Thang", so will be ;

    last data shown
    Sincerely,
    blank cell
    blank cell
    blank cell
    Robert Thang
    #3564

    once again, keep based on last data above and with "e" chosen in K3

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    Re: Display text based on last data row

    In K8 as an array, copied all the way down

    =IF(K7="total","Sincerely", IF(OR(K4="Total", K5="Total", K6="Total"),"", IF(K3="Total","Robert Thang", IF(K2="Total", "#3564",IF(COUNTIF($K$5:K7,"total")>0,"",IF(AND($K$5="date",$K7=""),"total",IFERROR(INDEX(A$6:A$1002,SMALL(IF($B$6:$B$1002=$K$3,ROW($B$6:$B$1002)-ROW($B$6)+1),ROWS($B$6:B8))),"")))))))
    See attachment
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2010 & 2016 (NOT PT or PQ)
    Posts
    1,049

    Re: Display text based on last data row

    hi, chemistB, thank you very much...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Raw data column text to new worksheet - display data based on "Yes" filter
    By tsgallivan12 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-11-2013, 01:56 PM
  2. [SOLVED] Display text based on content of 3 cells
    By smugglersblues in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 12-20-2012, 12:25 PM
  3. Complete Text to Display Based on Number
    By BlastRanger in forum Excel General
    Replies: 2
    Last Post: 09-30-2010, 04:39 AM
  4. Display different text based on data
    By bergtho in forum Excel General
    Replies: 4
    Last Post: 08-18-2009, 08:53 AM
  5. [SOLVED] Display text based on a numeric range
    By swheeler in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2005, 11:06 AM

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