+ Reply to Thread
Results 1 to 8 of 8

Find non-empty cell in row and apply formula to that cell only

  1. #1
    Registered User
    Join Date
    12-17-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    10

    Find non-empty cell in row and apply formula to that cell only

    Hi,

    I have a table with 12 columns and 800 odd rows, each row only has one value in it situated in any of the 12 columns. Most of the values are made up of multiple parts (eg =45+56 or =12+89+13 etc) and so I have a formula to find how many parts are in each non-blank cell.
    The formula is [=LEN(Frmula)-LEN(SUBSTITUTE(Frmula,"+",""))+1] the "Frmula" being an XLM 4 macro to turn the cell to the left into a text string which allows the Len and Substitute formulas to return the number of plus signs in the cell.

    Is it possible to find the non-empty cell in the row and then apply the above formula to it to return the number of parts in the cell?

    For example, if the only value in the table row is in column 3, then i need a formula to find that the only value in the row is in column 3 and then imbed my formula above to return the result i am looking for.

    I was thinking of using a Match/Index function, but I'm not sure how i can imbed my formula to produce the result.

    Cheers
    Last edited by mini_dutch28; 03-20-2013 at 10:34 PM.

  2. #2
    Forum Contributor
    Join Date
    03-12-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2007
    Posts
    227

    Re: Find non-empty cell in row and apply formula to that cell only

    I just created a mock row in Excel from column A to C, so you will need to change the row/column references to fit your wksh.

    =MATCH(SUM(A1:C1),A1:C1,0)

  3. #3
    Registered User
    Join Date
    12-17-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Find non-empty cell in row and apply formula to that cell only

    Hi majosum,

    the formula you have provided above gives me the column number of the non-empty cell in the row.

    how do i use this to apply another formula to the value at the column reference?

    for example, if my value is in column 3, and the above formula you have provided returns a value of 3, how do i now go about applying a formula to the value at that reference point?

  4. #4
    Forum Contributor
    Join Date
    03-12-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2007
    Posts
    227

    Re: Find non-empty cell in row and apply formula to that cell only

    In what column is the =LEN() formula and what do you mean it turns the cell to the left into a text string?

  5. #5
    Registered User
    Join Date
    12-17-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Find non-empty cell in row and apply formula to that cell only

    currently the Len formula only works if it is situated in the cell to the right of the value; so if the value was in cell B1, the len formula would be in cell C1.

    the full formula i have is below:

    =LEN(GET.CELL(6,OFFSET(INDIRECT("RC",FALSE),0,-1)))-LEN(SUBSTITUTE(GET.CELL(6,OFFSET(INDIRECT("RC",FALSE),0,-1)),"+",""))

    the (GET.CELL(6,OFFSET(INDIRECT("RC",FALSE),0,-1)) part of the formula turns the cell to the left into a text string, which then allows the Len and substiture formulas to find how many plus signs are in the formula. if it was not turned into a text string, the len and substitute formulas wouldnt be able to return the correct number of plus signs in the cell.

    the issue i face is the fact that i cant manually input the formula in the cell to the right of the value as it would take forever as there are almost 1000 rows, thus i am trying to find a formula, such as your MATCH formula above to find the position of the value in the row and apply my formula described above.

  6. #6
    Forum Contributor
    Join Date
    03-12-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2007
    Posts
    227

    Re: Find non-empty cell in row and apply formula to that cell only

    That formula looks confusing, but if I am not mistaken, the "-1" in the offset is used to look to the left one cell right? Instead of using -1, see if you can substitute the following portion for the -1 and place the entire formula in the 13th column and drag down. Be sure to keep that negative sign at the beginning and once again, you'll need to change the cells to match your worksheet.

    -(COLUMN(D1)-MATCH(SUM(A1:C1),A1:C1,0))

  7. #7
    Forum Contributor
    Join Date
    03-12-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2007
    Posts
    227

    Re: Find non-empty cell in row and apply formula to that cell only

    Sorry, I forgot to say. D1 was the cell that I typed the formula into.

  8. #8
    Registered User
    Join Date
    12-17-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Find non-empty cell in row and apply formula to that cell only

    so i used part of your formula and it has worked, however i did modify it a bit.

    when using the match formula it counts from the left, but the formula i was using was counting backwards from the right, so the cell being referenced was empty. so instead of the column(D1) part, i substituted the -1 at the end of my Offset formula to the following: -(13-match(sum(E6:P6),E6:P6,0)) this using the value from the match formula (eg 3) and counts back 10 cells (13-3) to the 3rd cell to return the correct value.

    thanks for your help!

+ 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