Consulting

Results 1 to 11 of 11

Thread: Solved: Check Box Formula

  1. #1
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    244
    Location

    Solved: Check Box Formula

    I am trying to get this formula to read if check box is true and show nothing if it shows false.

    This is the formula

    =IF(X2=TRUE,=INDEX,Diagrams!$C:$C,MATCH(C2,Diagrams!$A:$A,0)+ROW($A$1)-1),"")

    Is this able to work as such or should I be looking at another approach.

    This part of the formula =INDEX,Diagrams!$C:$C,MATCH(C2,Diagrams!$A:$A,0)+ROW($A$1)-1)

    This part is looking up another sheet and works fine, just trying to add the check box part if possible.

    Thanks for any assistance.

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    Ride the wind to the sun
    Posts
    2,598
    does your formula contain equal sign "=" before "INDEX"?
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    244
    Location
    Yes, I have tried it with and without the = sign but have had no luck.

  4. #4
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Barryj

    Have you assigned x2 to the cell in properties?

  5. #5
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    244
    Location
    Yes X2 is the cell linked to the check box.

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,834
    Location
    In thinking about this issue, the checkbox is an object placed on the worksheet, yet it seems you are checking the cell location.
    Last edited by Aussiebear; 10-28-2011 at 02:41 AM. Reason: Never mind: I'm simply walking in circles here....
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    Ride the wind to the sun
    Posts
    2,598
    imho, a formula with two = signs must give an error.

    you removed and did not work...
    can you post the workbook with representative data.
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  8. #8
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    Ride the wind to the sun
    Posts
    2,598
    am i missing something?

    should it be
    [VBA]=INDEX(Diagrams!$C:$C,MATCH(C2,Diagrams!$A:$A,0)+ROW($A$1)-1)[/VBA]

    rather than
    [VBA]=INDEX,Diagrams!$C:$C,MATCH(C2,Diagrams!$A:$A,0)+ROW($A$1)-1)[/VBA]
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  9. #9
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    244
    Location
    Ok I have got it mostly working with
    [VBA]=IF(X2=TRUE,INDEX(Diagrams!$F:$F,MATCH(B2,Diagrams!$A:$A,0)+ROW($A$1)-1))[/VBA] it displays the result that I want when cell X2 displays TRUE.

    But when the check box is not checked it displays false in the cell where I have the formula and the desired result.

    As a work around I have custom formatted the cell to white text if it displays false.

    Can I add to the formula to show the cell blank when X2 shows FALSE?

  10. #10
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    Ride the wind to the sun
    Posts
    2,598
    referring to the formula in post#1
    [VBA]=If(X2=True,INDEX(Diagrams!$F:$F,MATCH(B2,Diagrams!$A:$A,0)+ROW($A$1)-1),"")[/VBA]
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  11. #11
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    244
    Location
    Thankyou mancubus, it is now working fine, I will mark this thread as solved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •