+ Reply to Thread
Results 1 to 12 of 12

Populating Cells conditionally

Hybrid View

bungaree Populating Cells conditionally 01-07-2016, 02:39 PM
JeteMc Re: Populating Cells... 01-07-2016, 03:13 PM
bungaree Re: Populating Cells... 01-07-2016, 03:20 PM
JeteMc Re: Populating Cells... 01-07-2016, 03:26 PM
bungaree Re: Populating Cells... 01-07-2016, 03:43 PM
JeteMc Re: Populating Cells... 01-07-2016, 03:55 PM
bungaree Re: Populating Cells... 01-07-2016, 04:07 PM
JeteMc Re: Populating Cells... 01-07-2016, 04:31 PM
bungaree Re: Populating Cells... 01-07-2016, 04:49 PM
JeteMc Re: Populating Cells... 01-07-2016, 06:17 PM
bungaree Re: Populating Cells... 01-07-2016, 08:30 PM
JeteMc Re: Populating Cells... 01-07-2016, 09:47 PM
  1. #1
    Registered User
    Join Date
    12-26-2006
    Posts
    78

    Populating Cells conditionally

    Hello Forum,

    Please can you assist with a formula that's doing my head in!

    I've attached a file with note which I hope are clear enough.

    Many thanks for having a look -- here are the conditions which I also have in the doc:



    All changing data cells (cols BN-BQ) have formulas in them in original doc, ie they are not blank so isblank etc won't work

    If columns BP BQ are blank: No data should appear in cols BT-BV

    If both cols BP BQ have data, BQ appears in BV, and BP appears in BU

    If one or the other of cols BP BQ have data, that data appears in col BV

    Depending on which of Cols BP BQ now lead in col BV, look to the left for the numbers to appear in BU and BT

    Until there's a blank cell (though there's still a formula there in the original doc), then stop

    Conditional.xlsx

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    19,043

    Re: Populating Cells conditionally

    Only a partial solution. This formula in BT29 and down will get rid of the 1's and will also put a 3 in BT34:
    =IF(AND(ISNUMBER(BQ29),ISNUMBER(BP29),ISNUMBER(BO29)),BO29,IF(OR(AND(ISNUMBER(BQ29),ISNUMBER(BO29),ISNUMBER(BN29)),AND(ISNUMBER(BP29),ISNUMBER(BO29),ISNUMBER(BN29))),BN29,""))
    However it raises a question of why there should not be a 3 in BT35 and a 1 in BU35? Here is the file with the updated formula applied:
    Copy of Conditional.xlsx
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    12-26-2006
    Posts
    78

    Re: Populating Cells conditionally

    JeteMc, thank you so much for your quick response. You are right, there shouldn't be a 3 in BT35 and a 1 in BU35 - my apology.

    Is it possible for you to adapt the formula?

    Many thanks!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    19,043

    Re: Populating Cells conditionally

    Try this formula in BU29 and down:
    =IF(AND(ISNUMBER(BQ29),ISNUMBER(BP29)),BP29,IF(OR(ISNUMBER(BP29),ISNUMBER(BQ29)),BO29,""))
    Let me know if you have any questions.

  5. #5
    Registered User
    Join Date
    12-26-2006
    Posts
    78

    Re: Populating Cells conditionally

    Ah, sorry, but that one doesn't work as it doesn't take into account the blanks.

    Working from R to L, when a blank is encountered, no more data is added.

    The formula I originally put into col BU works OK - it's the formulas in BT that are misbehaving.....

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    19,043

    Re: Populating Cells conditionally

    I am confused, the original formula for BU left the 1 out of BU35, so how can it work? Also what is the revised formula including or not including in BT?

  7. #7
    Registered User
    Join Date
    12-26-2006
    Posts
    78

    Re: Populating Cells conditionally

    Yes, there shouldn't be a 1 in BU35 because of the space in BP 35 - once there's a space, no further data comes across.

    It's the formula in BT that's misbehaving.

    I know it's confusing - my head's had it!!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    19,043

    Re: Populating Cells conditionally

    I'd suggest re-attaching the updated file and marking or highlighting the errors that the formula is causing.

  9. #9
    Registered User
    Join Date
    12-26-2006
    Posts
    78

    Re: Populating Cells conditionally

    Hi JeteMc, the file in the original post above has the formulas highlighted in pink, plus a note above to point to the problem formulas - please let me know if you need me to upload again? Many thanks for persisting

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    19,043

    Re: Populating Cells conditionally

    Sorry about that, here is a formula for BT29 and down that will yield the results you want in that column:
    =IF(AND(BP29="",BQ29=""),"",IF(AND(BQ29="",ISNUMBER(BP29),ISNUMBER(BN29)),BN29,IF(AND(ISNUMBER(BQ29),ISNUMBER(BP29),ISNUMBER(BO29)),BO29,"")))
    This will also cover the case, which was not included in the sample file, where BQ, BP and BO all have values.

  11. #11
    Registered User
    Join Date
    12-26-2006
    Posts
    78

    Re: Populating Cells conditionally

    JeteMc, THANK YOU!!! It's perfect - I'm so grateful and thank you again for sticking with it

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    19,043

    Re: Populating Cells conditionally

    You're Welcome, and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. Hope that you have a good day.

+ 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] Conditionally Lock Cells
    By Gos-C in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2015, 04:24 PM
  2. Auto skip colored cells VBA but include conditionally formatted cells also
    By Cremorneguy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2014, 03:44 AM
  3. Macro to Conditionally Format Cells based on another cells value
    By Barking_Mad in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-07-2013, 03:05 AM
  4. Replies: 7
    Last Post: 01-28-2011, 10:07 AM
  5. Conditionally Populating a Sheet from Mulitple Sheets
    By Odysimus in forum Excel General
    Replies: 0
    Last Post: 12-08-2009, 06:31 PM
  6. [SOLVED] How do I conditionally lock cells?
    By wahamler in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2005, 05:05 PM
  7. conditionally format cells
    By elad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2005, 01:06 PM

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