+ Reply to Thread
Results 1 to 4 of 4

concatenate mixed with find last value in row

  1. #1
    Registered User
    Join Date
    05-17-2010
    Location
    Canberra Australia
    MS-Off Ver
    Excel 2003
    Posts
    1

    concatenate mixed with find last value in row

    Hi, I need a little help with CONCATENATE

    I already have a form that finds the last cell with data in sheet 1 then adds the new data to the row beneath (pretty standard thing to do and I’ve got that working).

    What I need help with is, once that data is entered I want my concatenate formula in sheet 2 to write a little blurb about the latest entry (so staff can highlight it and paste it into another program).

    So in sheet 1 I have data like
    A B C
    1 Type Size Colour
    2 Truck Small Blue
    3 Car Big Red

    In sheet 2 I have =CONCATENATE(Sheet1!A3,Sheet3!A1……….)

    In sheet 3 I have strings like ‘This is a’ to concatenate with the data from sheet 1.

    So the end product should be a small narrative in sheet 2 that reads something like ‘This is a Big car that is the colour red’

    What I can’t do is make reference to sheet 1 in the CONCATENATE formula. In the example above I would need to reference the last row with text, rather than type A3.

    So when the next entry is added to sheet one, let’s say boat – small – white
    The narrative in sheet 2 will change to read ‘This is a small boat that is the colour white’

    Can anyone help? What I need is the formula for CONCATENATE that reads (In Sheet 1 find the last cell with data in column A, Sheet3!A1......) Instead of (Sheet1!A3, Sheet3!A1…)

    Thanking you in advance

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: concatenate mixed with find last value in row

    Kevin 07
    I know its you
    I'm confused can you please attach a small work book?
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Registered User
    Join Date
    08-29-2007
    Posts
    47

    Re: concatenate mixed with find last value in row

    Here's one way it could work. It's not the prettiest formula, someone could possibly clean it up a bit.

    Please Login or Register  to view this content.
    Basically, it's counting the total number of non blank cells in A1. You can adjust A1:A100 to be larger if needed. It then says, use the total number of nonblank cells as the row number, and use the number I specify as the column number. Make sense?

  4. #4
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: concatenate mixed with find last value in row

    mjgoodyear;

    Try this;
    Please Login or Register  to view this content.
    I just realized a condition for this to work
    This assumes there are no empty cells in Column A
    Last edited by foxguy; 05-17-2010 at 12:12 PM. Reason: I just realized a condition for this to work
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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