+ Reply to Thread
Results 1 to 21 of 21

Help to clear a " False " Error code in Cell N13

  1. #1
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Help to clear a " False " Error code in Cell N13

    Good afternoon all

    I have a sheet that has about 100 nested IF statements and I am at the tail end of
    almost getting all the bugs out, except for when I enter either a 3 or a 4 into cell M13
    it is returning a "False " error any either digit is OK.

    What happens if you enter 2 into each cell M11:M13 you should get in the cells
    N11=1 2, N12=3 4, N13=56 and consequently each row should add along with
    each entry into cells M11, M12,M13.

    Cells " N and O " 13 have the formula's in them.

    Sample file attached

    Peter
    Attached Files Attached Files
    One I have learnt over the last few months, " You are NEVER too old to learn ".
    Sometimes I forget to say " Thank You " for your assistance, so here I say " A BIG Thank You. "

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    Let's start again with this, Pete. This is absolutely NOT the way to do this!

    Please answer the following questions:

    1. If the numbers 2, 4 and 2 appear in M11, M12 and M13 respectively, should the results returned in N11 to N13 be taken from row 5 or 19 of the result matrix?
    2. Why does the combination 2 4 2 appear twice in the lookup matrix and how is Excel meant to decide between them?
    3. Why are E12 and F12 blank?

    Please answer the questions without any further detail at this stage.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Help to clear a " False " Error code in Cell N13

    Good Morning Ali

    Re Q 1= The lookup could be anywhere from what you call the matrix.
    Re Q 2 =I most likely have several duplicated in that area
    Re Q 3=Nothing looks into that region for an answer.

    I wished I had enough knowledge to do the whole thing differently

    Pete

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    Oh, dear!

    OK - let's try again.

    By the way, I am not about to attempt to back engineer what you are trying to do from those monster formulae, so I'm only prepared to help IF you answer my questions properly.

    Question: which section of the matrix is being used for these calculations and which sections are redundant? Cell references, please.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    Waiting for an answer to my question.

  6. #6
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Help to clear a " False " Error code in Cell N13

    Ali

    I tried to use the trace dependent function and save the file but it drops off the pointers,
    MAY I suggest you try please using the forward and backward tracers to see which cells
    are in the matrix is being used.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    No, no, no!!!!

    Pete - I have asked a question. Please answer it. I am NOT going to hunt around trying to work things out from the mess you have created.

    If you want my help, then we do things my way. If not, I simply step aside and leave you to stew.

    Let's be clear here: I can give you a much simpler way to do this, but ONLY if you answer my questions.

    Question: which section of the matrix is being used for these calculations and which sections are redundant? Cell references, please. You have built those monster formulae, so you MUST know which sections of the matrix they use and which not.
    Last edited by AliGW; 08-05-2019 at 02:41 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    Let me try another tack. Forget the question above.

    What combination of numbers can appear in these cells?

    Excel 2016 (Windows) 32 bit
    L
    M
    11
    1st Leg
    12
    2nd Leg
    13
    3rd Leg
    Sheet: Demo

    1st leg: range of possible entries - 1 to 4? 2 to 6? what?

    Same for the other two.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    Any response to post #8, Pete? Do you want any help with this?

  10. #10
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Help to clear a " False " Error code in Cell N13

    Ali

    I have used the Forward and reverse trace functions and removed those cells NOT being looked
    at for an answer and attached it as a file which is easier to read than me trying to paste the
    results here, HOPEFULLY it helps.

    Re your post 8 I would expect 1st leg anywhere between 2 and 5, then subject to what is in 11,
    12 will be leg one PLUS (Lets say 6 to 10, and leg 3 ( Lets say 11 to 14 ).

    Is this OK

    P
    Attached Files Attached Files
    Last edited by Potholes; 08-05-2019 at 03:18 AM. Reason: Frgot attachment

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    No. Never mind what MIGHT be in the preceding cell. That comes later.

    What is the range of numbers that could UNDER ANY CIRCUMSTANCES appear in M11?

    And in M12 (regardless of M11)?

    And in M13 (regardless of M11 and M12)?

    Simple questions to which I expect a simple answer (minimum and maximum number of checkpoints in each leg).

    Excel 2016 (Windows) 32 bit
    L
    M
    11
    1st Leg
    range?
    12
    2nd Leg
    range?
    13
    3rd Leg
    range?
    Sheet: Demo

    I will look at the updated workbook, but the way you have had to cut it down does not fill me with confidence, I have to say.

    EDIT: I've looked and it really doesn't help, so we'll do this from scratch, starting with your answers (3) to the simple questions posed in this post.
    Last edited by AliGW; 08-05-2019 at 03:26 AM.

  12. #12
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Help to clear a " False " Error code in Cell N13

    What I did was to remove the cells that are not being looked at.

    Lets assume that on the day they have a total of 10 checkpoints for the day
    In leg 1 we have Checkpoints 1 & 2
    In Leg 2 we have Checkpoints 3 to 7
    then in Leg 3 we have checkpoints 8 to10.
    Depending on how many checkpoints they allocate to each leg on the day
    it may well only have 6 for the day and only using 2 legs.

    As far as to the maximum number for each leg FIVE per any leg.


    If you have a quicker way to do this I AM ALL EARS..


    P

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    What's the MAXIMUM number of checkpoints per day?

    What is the MINIMUM number of checkpoints per leg? 2?

    What is the MAXIMUM number of checkpoints per leg? 5?
    Last edited by AliGW; 08-05-2019 at 03:43 AM.

  14. #14
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Help to clear a " False " Error code in Cell N13

    What's the MAXIMUM number of checkpoints per day? 15

    What is the MINIMUM number of checkpoints per leg? 2? 2

    What is the MAXIMUM number of checkpoints per leg? 5? 5

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    One final quetion: what would the MAXIMUM number of checkpoints be for a 2-leg race?

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    Try this for size. Change JUST the yellow cells to make sure that any potential combination works. My demo is on Sheet 1.
    Attached Files Attached Files

  17. #17
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Help to clear a " False " Error code in Cell N13

    Ali

    I was allowed to I would say I love you BUT we are not SOOOO

    I will say a VERY BIG THANK YOU.

    I will be using your form

    Sorry If I am being a pain in the Tooch, OLD age is my excuse.

    I wish there was a way we could recompense you people for your time and
    patience.

    If you of the mind to would you please explain how you made that table and what is it called please.

    Peter

    Sorry but the system will NOT allow me to give you more REP.
    Last edited by Potholes; 08-05-2019 at 04:15 AM.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    You are welcome, Peter. Let's put it this way, 'tidy' and 'logical' are not adjectives I'd use to describe your aging brain, and that's what you need to be when approaching something like this. I expect there are even smarter ways of doing it, but this is my way and I'll put together an explanation for you.

    Essentially, though, you are using three tables (I know it looks like two, but it is in fact three). The first is being interrogated with VLOOKUP and the other two are being interrogated with INDEX MATCH as they are two-dimensional lookups. More in a bit ...

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    Excel 2016 (Windows) 32 bit
    I
    J
    7
    1st Leg =IF(J2="","",VLOOKUP(J2,$B$2:$C$5,2,0))
    8
    2nd Leg =IF(J3="","",INDEX($D$2:$G$5,MATCH(--TRIM(RIGHT(SUBSTITUTE(K2," ",REPT(" ",50)),50)),$B$2:$B$5,0),MATCH(J3,$D$1:$G$1,0)))
    9
    3rd Leg =IF(J4="","",INDEX($C$8:$F$16,MATCH(--TRIM(RIGHT(SUBSTITUTE(K3," ",REPT(" ",50)),50)),$B$8:$B$16,0),MATCH(J4,$C$7:$F$7,0)))
    Sheet: Sheet1

    =IF(J2="","",VLOOKUP(J2,$B$2:$C$5,2,0))

    This is using the table in B2:C5 to establish the first stage's checkpoints based on J2. Simple VLOOKUP - I don't think I need to say any more about this, as it's straightforward.

    --TRIM(RIGHT(SUBSTITUTE(K2," ",REPT(" ",50)),50))

    For the other two legs, this is used to extract the rightmost number from the cell above, in this case K2 (last number is 2) and convert it to a number value with the double negative (--).

    Then it's simply a case of INDEX MATCH MATCH using the following lookup tables:

    2nd Leg: B1:G5
    3rd Leg: B7:F16

    INDEX: the array from which the answer will come (either D2:G5 or C8:F16).

    1st MATCH (vertical lookup): to match the lookup value (last number in K2 or K3) with the vertical axis of the lookup table (B2:B5 or B8:B16).

    2nd MATCH (horizontal lookup): to match the lookup value (J3 or J4) with the horizontal axis of the lookup table (D1:G1 or C7:F7).

    The result of an INDEX MATCH MATCH is the intersection point in the INDEX range.

    I've added this explanation to your file (attached).

    PS I hope Mrs Potholes is feeling better.
    Attached Files Attached Files

  20. #20
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Help to clear a " False " Error code in Cell N13

    Dear Ali

    Many Thanks for that I have printed it out and will add it to the folder with the other learned material.

    I don't Know how she is Lynette is away about 1500km away with a longtime friend who is a 6'4" tall lady
    about 50 summers young and has the bones of a 101 summers person, and she was the one that got
    6 broken Ribs in the car accident that Lynette was in also, she is OK Perforated Ear Drum.
    Back on Thr YAHOOOOOOO

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,386

    Re: Help to clear a " False " Error code in Cell N13

    OK - that was another of your spaghetti missives, then, as I had understood Lynette to be the one with the broken ribs!

    Look after her when she gets home - I am sure she'll be shaken.

+ 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] Nested HLOOKUP returns "FALSE" in cell where result should be "0.00"
    By gammccubbin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2016, 09:49 AM
  2. [SOLVED] Code Clean-Up: Delete "False" worksheet created when "Cancel" is chosen in Input Box
    By Kenny Blackwell in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 08-31-2015, 10:00 AM
  3. [SOLVED] Test for blank cell should be "True" but "False" action is taken.
    By Aceso in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-09-2013, 05:47 PM
  4. Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" not working
    By redders in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2011, 03:52 PM
  5. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  6. Macro Error with code "Range("A65536").End(xlUp).EntireRow.Insert"
    By lukasj13 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-30-2010, 08:48 PM
  7. Replies: 7
    Last Post: 05-13-2006, 05:02 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