+ Reply to Thread
Results 1 to 14 of 14

If a cell in a column is not blank, insert the number in the same row in column x

  1. #1
    Registered User
    Join Date
    09-17-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    7

    If a cell in a column is not blank, insert the number in the same row in column x

    [SOLVED] Hi, I'm not very strong in Excel and am having difficulty creating a formula that does the following:

    If a cell in a column is not blank, insert the number from the same row in column x

    For example: If any cell in cells D7:D10 is not blank, insert the number in the same row in column C.
    So, if searching D7:D10 finds content in D:8, insert the number here from C:8.

    (Note: there will never be data in more than once cell in a column.)

    Thank you in advance for any help you can provide.
    Last edited by Kumait; 09-17-2013 at 04:51 PM. Reason: Question resolved.

  2. #2
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    Kumait, welcome to the forum.

    In C7, dragged down as far as required, type the following formula:

    Please Login or Register  to view this content.
    This tells C7 that IF D7 is blank/empty (""), return nothing (""); otherwise, return the value in D7.

    Hope this helps.
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  3. #3
    Registered User
    Join Date
    09-17-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    Brendan, Thanks for your welcome and your quick response.

    My apologies for not making my request very clear. I'll try to explain it better: I have several contiguous cells in a column (e.g., D7, D8, D9, and D10). At the bottom of that cluster of cells (e.g., D11, where I am inserting the formula), I want to insert a value. That value will depend on which row the not-blank cell is located in (e.g., D8). If D:8 is the one cell where there is content in the specified column of cells, I want the formula to find the value in column C of the same row (e.g. C8) and insert it in D:11.

    I hope that helps clarify.

    Thanks again for helping.

  4. #4
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    Ok so, try this in D11:

    Please Login or Register  to view this content.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    Here's another way...

    =LOOKUP(1E100,C7:C10)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    09-17-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    Brendan, Works perfectly. Thank you!
    Last edited by Kumait; 09-19-2013 at 06:38 PM.

  7. #7
    Registered User
    Join Date
    09-17-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    Thank you, too, Tony. This not only works but is a work of art. I wish I understood how it works, but it works great.

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    Here's how it works...

    =LOOKUP(1E100,C7:C10)

    1E100 is scientific notation, a shorthand method of expressing very long numbers. 1E100 is the very large number 1 followed by 100 zeros.

    In this application, if the lookup_value is greater than any number in the referenced range then the formula will return the last (bottom-most) number in the range.

    To ensure that this happens we use an arbitrary gigantic number like 1E100. There is a 100% chance that the numbers you are dealing with are nowhere near as large as 1E100. In fact, the lookup_value simply needs to be greater than any number in the range. So, if your data represents bowling scores then you can use any number greater than 300 (the maximum possible bowling score) as the lookup_value:

    =LOOKUP(301,C7:C10)

    As a second thought, if there will only be a single number in the range you can use something as simple as:

    =MAX(C7:C10)

  9. #9
    Registered User
    Join Date
    09-17-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    Tony,
    I tried out your formula in different columns, but it did not work as well as it did the first time (in column D). The problem is that the formula automatically returns the value in C:10 (which happens to be 100), no matter what the content of cells D7:D10.

    Let me be more specific. Cells C7:C10 contain numbers in ascending values: C7=25, C8=50, C9=75, and C10=100. If cell D7 is the one that is not blank, then the formula should return the value "25". Similarly, if D9 is the one that is not blank, then the formula should return the value of "75". But the formula you recommend returns a value of "100" no matter which cell in the column is not blank.

    Thanks again.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    OK, now I understand what you want to do.

    What type of data is in D7:D10 ? Is it text? Numbers? Could be both? Something else? Are there formulas that return formula blanks?

  11. #11
    Registered User
    Join Date
    09-17-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    Quote Originally Posted by Tony Valko View Post
    OK, now I understand what you want to do.

    What type of data is in D7:D10 ? Is it text? Numbers? Could be both? Something else? Are there formulas that return formula blanks?
    Numbers and/or text. Here is an example of what it looks like (I hope it looks the way it does now when I submit the reply:

    Col. A Col. C Col. D Col. E
    ----------- ------ ------- -------
    Points Co. ABC Co. XYZ
    <$10M 0
    $11-20M 25 $12.7M
    $21-30M 50
    $31-40M 75 $39.9M
    $41-50M 100

    Thanks

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    Maybe this...

    Data Range
    C
    D
    E
    7
    0
    8
    25
    $12.7M
    9
    50
    10
    75
    $39.9M
    11
    100
    12
    25
    75

    This formula entered in D12 and copied across:

    =SUMIF(D7:D11,"<>",$C7:$C11)

  13. #13
    Registered User
    Join Date
    09-17-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    That did not seem to work either. But the answer by Brendan (BB1972) works. I greatly appreciate your efforts. Thanks again for being so responsive.

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If a cell in a column is not blank, insert the number in the same row in column x

    Good deal. Thanks for the feedback!

+ 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. [SOLVED] Need to insert a number into the next blank cell in a column
    By Pete the excel noob in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-22-2013, 02:21 AM
  2. Replies: 4
    Last Post: 03-13-2013, 12:39 AM
  3. [SOLVED] Compare 4 column values then insert a 5th columns value to a blank column/cell value
    By JasonKMcCoy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-27-2012, 06:01 PM
  4. cannot insert column due to non blank cell
    By stevekirk in forum Excel General
    Replies: 3
    Last Post: 04-20-2008, 11:10 PM
  5. insert a blank column if a cell is blank...how?
    By bego in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2007, 03:04 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