Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    May 2016
    Posts
    8
    Thanks
    0
    Thanked 1 Time in 1 Post

    Talking Multi-Sheet Referencing & Validation

    Hi Guru's
    Require a macro, formulas / validation to assist me in the following for a spreadsheet with multiple sheets and tables / lists.
    • Multiple Selection Drop Box which enters the selections into a cell.



    • Cell $D$10 to look to a cell from another sheet (if that cell is not blank) then, enter the contents of another cell from another sheet / list.
    • Once that is completed cell $E$10 to look for contents of cell $D$10 and then enable a specific drop box
    • Cell $D$11 to look to a cell from another sheet (if that cell is not blank) then, enter the contents of another cell from another sheet / list.
    • Once that is completed cell $E$11 to look for contents of cell $E$11 and then enable a specific drop box different to above.
    • If Cell $D$10 or $E$10 or $F$10 are changed then the following cells in set default to blank (I have written the VBA code for this - see below)
    • Cells $H$10:$K$10 and $M$10:$P$10 are drop boxed to cause concatenation and VLookup. (Working well) =IF(I10="","",IF(I10>0,VLOOKUP(CONCATENATE(H10&I10 ),(Tables!$D$20:$H$44),5,FALSE)))
    • Cell $L$10 to have Multiple Selection Drop Box which lists the contents separated by comma's. help!


    Cell $Q$10 has drop down box with 10 choices. If any choice other than "eliminated" is selected is should show "false" in $S$10 which has the following formula
    • Help with formulas to change from "true" / "false" to Specific Text: eliminated / not elliminated


    =IF(N10:N13="","",AND(COUNTBLANK(Q10:Q13)=0,SUMPRO DUCT((Q10:Q13<>"")/COUNTIF(Q10:Q13,Q10:Q13&""))=1))

    Cell $O$10 is concatenated with VLookup to reveal "Low" "Medium" "High" or "Very High"
    =IF(N10:N13="","",AND(COUNTBLANK(O10:O13)=0,SUMPRO DUCT((O10:O13<>"")/COUNTIF(O10:O13,O10:O13&""))=1))
    • Help to change from "True" "False" to equal "low" or not Low


    The two outcomes above cause $T$10 to be conditionally formatted either red colour if "No revalidation" or green colour if "Revalidation"
    Unfortunately the below is showing a false false revalidation
    True + True = Revalidation
    False + true = No Revalidation
    False + False = Revalidation (this should be No Revalidation)
    VBA code for Defaulting subsequent following cells:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    Select Case Target.Column
    Case 4 'in column D = reset other drow downs to blank
    Range("E" & Target.Row & ":G" & Target.Row).ClearContents

    Case 5 'in column E = reset other drow downs to blank
    Range("F" & Target.Row & ":G" & Target.Row).ClearContents

    Case 6 'in column F = reset other drow downs to blank
    Range("G" & Target.Row & ":G" & Target.Row).ClearContents

    Case Else
    'do nothing
    End Select

    End Sub

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Julien

    Welcome to the Lounge as a new poster!

    A meaty request indeed!
    ..it would help us to help you if you could attach a sample workbook.
    You can put dummy data in, and delete any sensitive data.
    It saves us a lot of time trying to create a test file from scratch, and means that the answers we give are more likely to be relevant to your needs.
    And, when we post back an updated file, it would then be much easier to explain what we did too.

    zeddy

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Julien: Welcome to the Lounge. For future reference, please give your threads meaningful topics. A topic titled Q is almost devoid of meaning and means people have to read the thread before being able to find out if it's something they might be able to help with. I've edited the title for this time around.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    New Lounger
    Join Date
    May 2016
    Posts
    8
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by zeddy View Post
    Hi Julien

    Welcome to the Lounge as a new poster!

    A meaty request indeed!
    ..it would help us to help you if you could attach a sample workbook.
    You can put dummy data in, and delete any sensitive data.
    It saves us a lot of time trying to create a test file from scratch, and means that the answers we give are more likely to be relevant to your needs.
    And, when we post back an updated file, it would then be much easier to explain what we did too.

    zeddy
    Thanks Zeddy - I forgot to attach the file, here it is
    Clarification - all assistance is for Confined Space Inherent Risk sheet (I can copy over to the Risk Generator tab later)

    Cell Confined Space inherent risk! $D$10:$D$13 to look to a cell from another sheet (Confined Space Identification $E$16:$E$19 & $F$16:$F$19 (if that cell is not blank) then, enter the contents of another cell (Tables!$K$10:$K$13)
    • Once that is completed cell $E$10 to look for contents of cell $D$10 and then enable a specific drop box (from Tables!L4:L27).
    • Cell $D$11:$D$13 to look to a cell from another sheet (if that cell is not blank) then, enter the contents of another cell from another sheet / list.
    • Once that is completed cell $E$11 to look for contents of cell $E$11 and then enable a specific drop box different to above. (see current Drop boxes)
    • If Cell $D$10 or $E$10 or $F$10 are changed then the following cells in set default to blank (I have written the VBA code for this - see below)
    • Cells $H$10:$K$10 and $M$10:$P$10 are drop boxed to cause concatenation and VLookup. (Working well) =IF(I10="","",IF(I10>0,VLOOKUP(CONCATENATE(H10&I10 ),(Tables!$D$20:$H$44),5,FALSE)))
    • Cell $L$10 to have Multiple Selection Drop Box which lists the contents separated by comma's. help! L10:L13 and I10:U113 (selecting from Tables!G4:G20)



    Cell $Q$10 has drop down box with 10 choices. If any choice other than "eliminated" is selected is should show "false" in $S$10 which has the following formula
    =IF(N10:N13="","",AND(COUNTBLANK(Q10:Q13)=0,SUMPRO DUCT((Q10:Q13<>"")/COUNTIF(Q10:Q13,Q10:Q13&""))=1))
    - Help with formulas to change from "true" / "false" to Specific Text: eliminated / not eliminated


    Cell $O$10 is concatenated with VLookup to reveal "Low" "Medium" "High" or "Very High"
    =IF(N10:N13="","",AND(COUNTBLANK(O10:O13)=0,SUMPRO DUCT((O10:O13<>"")/COUNTIF(O10:O13,O10:O13&""))=1))
    - Help to change from "True" "False" to equal "low" or not Low

    The two outcomes above cause $T$10 to be conditionally formatted either red colour if "No revalidation" or green colour if "Revalidation"
    Unfortunately the below is showing a false false revalidation
    True + True = Revalidation
    False + true = No Revalidation
    False + False = Revalidation (this should be No Revalidation)
    VBA code for Defaulting subsequent following cells:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    Select Case Target.Column
    Case 4 'in column D = reset other drow downs to blank
    Range("E" & Target.Row & ":G" & Target.Row).ClearContents

    Case 5 'in column E = reset other drow downs to blank
    Range("F" & Target.Row & ":G" & Target.Row).ClearContents

    Case 6 'in column F = reset other drow downs to blank
    Range("G" & Target.Row & ":G" & Target.Row).ClearContentsv
    Thanks Very much Zeddy
    Most needed and thanks in advance
    J
    Attached Files Attached Files

  5. The Following User Says Thank You to JulienMcMahon For This Useful Post:

    Jaggi (2016-06-02)

  6. #5
    New Lounger
    Join Date
    May 2016
    Posts
    8
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by macropod View Post
    Julien: Welcome to the Lounge. For future reference, please give your threads meaningful topics. A topic titled Q is almost devoid of meaning and means people have to read the thread before being able to find out if it's something they might be able to help with. I've edited the title for this time around.
    Cheers I appreciate it - I missed that important step! - Quote for the day: "Be strong enough to stand alone, smart enough to know when you need help, and brave enough to ask for it"

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Julien

    I've done all the fixes.
    I just need to do some more checking before I post the updated file back.

    zeddy

  8. #7
    New Lounger
    Join Date
    May 2016
    Posts
    8
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by zeddy View Post
    Hi Julien

    I've done all the fixes.
    I just need to do some more checking before I post the updated file back.

    zeddy
    Cheers Zeddy
    I am looking forward to receiving it.
    I really appreciate it and hope that the VBA's and Formulas are of help to others who may be trying to do similar.
    The things that excel can do, and the people who can make it do the most wonderful things, amazes me!
    Excitedly waiting....
    Jules

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Jules

    I have attached my updated file.
    I have numbered my comments below, so that you can refer to them in any reply to this post.

    Cell Confined Space inherent risk! $D$10:$D$13 to look to a cell from another sheet (Confined Space Identification $E$16:$E$19 & $F$16:$F$19 (if that cell is not blank) then, enter the contents of another cell (Tables!$K$10:$K$13)
    1. zeddy: formulas in [D10] to [D13] now returns Un-wanted event from sheet [Tables] if a risk is selected on sheet [Confined Space Identification]; if a risk is NOT selected (e.g. item "4. A concentration of airborne contaminant.." is NOT selected), there shouldn't be any need for a User to enter data for that particular row on sheet [Confined Space Inherent Risk], so I have applied a conditional format to 'grey out' cells showing no User input is expected. (This could be enhanced by preventing User input in such cells, rather than just showing grey cells for 'input not expected').

    Once that is completed cell $E$10 to look for contents of cell $D$10 and then enable a specific drop box (from Tables!L4:L27).
    2. zeddy: Data Validation in column [E10:E13] uses 'INDIRECT' formula to show the relevant specific dropdowns

    Cell $D$11:$D$13 to look to a cell from another sheet (if that cell is not blank) then, enter the contents of another cell from another sheet / list.
    3. zeddy: as per 1.

    Once that is completed cell $E$11 to look for contents of cell $E$11 and then enable a specific drop box different to above. (see current Drop boxes)
    4. zeddy: as per 2

    If Cell $D$10 or $E$10 or $F$10 are changed then the following cells in set default to blank (I have written the VBA code for this - see below)
    5. zeddy: Cell $D$10 should NOT be changed directly (it's a formula).

    Cells $H$10:$K$10 and $M$10:$P$10 are drop boxed to cause concatenation and VLookup. (Working well) =IF(I10="","",IF(I10>0,VLOOKUP(CONCATENATE(H10&I10 ),(Tables!$D$20:$H$44),5,FALSE)))
    6. zeddy: I simplified the formulas in [J10:K13]

    Cell $L$10 to have Multiple Selection Drop Box which lists the contents separated by comma's. help! L10:L13 and I10:U113 (selecting from Tables!G4:G20)
    7. zeddy: Tables!G4:G20 ????? please advise!

    Cell $Q$10 has drop down box with 10 choices. If any choice other than "eliminated" is selected is should show "false" in $S$10 which has the following formula
    =IF(N10:N13="","",AND(COUNTBLANK(Q10:Q13)=0,SUMPRO DUCT((Q10:Q13<>"")/COUNTIF(Q10:Q13,Q10:Q13&""))=1))
    - Help with formulas to change from "true" / "false" to Specific Text: eliminated / not eliminated
    8. zeddy: I think you mean $R$10 rather than $S$10. Formula in $R$10 shows eliminated / not eliminated.

    Cell $O$10 is concatenated with VLookup to reveal "Low" "Medium" "High" or "Very High"
    =IF(N10:N13="","",AND(COUNTBLANK(O10:O13)=0,SUMPRO DUCT((O10:O13<>"")/COUNTIF(O10:O13,O10:O13&""))=1))
    - Help to change from "True" "False" to equal "low" or not Low
    9. zeddy: formulas in [O10:O13] now shows "low" or "not low"

    The two outcomes above cause $T$10 to be conditionally formatted either red colour if "No revalidation" or green colour if "Revalidation"
    Unfortunately the below is showing a false false revalidation
    True + True = Revalidation
    False + true = No Revalidation
    False + False = Revalidation (this should be No Revalidation)
    10. zeddy: formula in $T$10 amended. "Revalidation" only shown if "eliminated" and "all low", otherwise "No Revalidation"

    VBA code for Defaulting subsequent following cells.."
    11. zeddy: VBA code has been updated.

    Since col D is a formula cell, this shouldn't be changed by the User.
    (They can 'untick' the risk on sheet [Confined Space Identification] if required)
    If User deletes an entry in columns [E] of [F], the dropdowns to the right will be cleared as requested (what about clearing other entries in that row??)

    12. zeddy: I have added two padlocks in top-left corner of sheet [Confined Space Identification]. Clicking the 'locked padlock' will set the sheet to 'protected', allowing User to select only data-input cells. Clicking the 'unlocked padlock' will cancel all sheet protection. (No passwords have be set, but could be)

    Hope you find this useful.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2016-05-19 at 11:28.

  10. #9
    New Lounger
    Join Date
    May 2016
    Posts
    8
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by zeddy View Post
    Hi Jules

    I have attached my updated file.
    I have numbered my comments below, so that you can refer to them in any reply to this post.


    1. zeddy: formulas in [D10] to [D13] now returns Un-wanted event from sheet [Tables] if a risk is selected on sheet [Confined Space Identification]; if a risk is NOT selected (e.g. item "4. A concentration of airborne contaminant.." is NOT selected), there shouldn't be any need for a User to enter data for that particular row on sheet [Confined Space Inherent Risk], so I have applied a conditional format to 'grey out' cells showing no User input is expected. (This could be enhanced by preventing User input in such cells, rather than just showing grey cells for 'input not expected').

    I see your thought process and the idea however by greying out the line for a "non event" as it were, does not show due diligence for risk management. Instead of greying out, can a similar action be undertaken for when Confined Space Identification!F13:19 is notblank? so ....
    =IF('Confined Space Identification'!E19="","",Tables!K7) BUT =IF('Confined Space Identification'!F18="NOTBLANK"....,
    D13 would still equal Tables!K16 but E13:G13 would state ""Not an confined Sapce inherent Risk"?

    I think then the Confined Space identification E13:F19 would need the input to be one or the other. If E13 is checked makes F13 unchecked and vice versa. but both can be blank and cause no change to Inherent risk D10. IS this possible?



    2. zeddy: Data Validation in column [E10:E13] uses 'INDIRECT' formula to show the relevant specific dropdowns
    Nice

    3. zeddy: as per 1.
    NICE NICE

    4. zeddy: as per 2
    NICE NICE NICE

    5. zeddy: Cell $D$10 should NOT be changed directly (it's a formula).
    Understood

    6. zeddy: I simplified the formulas in [J10:K13]
    oooohhhhh I like! Never thought to use IFERROR!
    7. zeddy: Tables!G4:G20 ????? please advise!
    My bad should be Q4:Q20

    8. zeddy: I think you mean $R$10 rather than $S$10. Formula in $R$10 shows eliminated / not eliminated.
    Oops may bad Again. You are correct

    9. zeddy: formulas in [O10:O13] now shows "low" or "not low"
    May bad third time in a row - )O10:O13 must stay as before (other than change to concatenation formula.. This is a standard set out) need to source another way of Not low criteria.

    10. zeddy: formula in $T$10 amended. "Revalidation" only shown if "eliminated" and "all low", otherwise "No Revalidation"
    Nicely doine sir. Very simple and very effective

    11. zeddy: VBA code has been updated.

    Since col D is a formula cell, this shouldn't be changed by the User.
    (They can 'untick' the risk on sheet [Confined Space Identification] if required)
    If User deletes an entry in columns [E] of [F], the dropdowns to the right will be cleared as requested (what about clearing other entries in that row??)
    Very correct of you to point out. Can this be done?

    12. zeddy: I have added two padlocks in top-left corner of sheet [Confined Space Identification]. Clicking the 'locked padlock' will set the sheet to 'protected', allowing User to select only data-input cells. Clicking the 'unlocked padlock' will cancel all sheet protection. (No passwords have be set, but could be)
    That's Handy! Saves a little time and effort

    Hope you find this useful.
    I do! very much! thank you sir
    I will bounce back a reply specifically about some further assist6ance for 1, 9 and 11 above.


    zeddy
    You beauty. Mate!
    Jules

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Jules

    ..I shall be off the grid for the next few days so I thought I'd post what I've done so far.

    In the attached file:

    On sheet [Confined Space Identification]:
    Double-clicking in the Yes/No range of columns [E:F] will Tick/untick the double-clicked cell.
    You cannot have BOTH a Yes and a No for the same item, so selecting a Yes will clear any No, and vice versa.
    You can leave both cells as blank i.e. Yes/No not selected.

    On sheet [Confined Space Identification]:
    If both Yes/No cells are blank on sheet [Confined Space Identification], conditional format is used to 'grey out' cells, showing no User input is expected. The input cells in the row are 'locked' to prevent User input in such cells.

    If a Yes has been selected (on previous sheet), User input is allowed, and cells are unlocked.

    If a No has been selected (on previous sheet), corresponding entry in col [D] has a 'red line' shown, and a textbox message "Not a Confined Space Inherent Risk' is displayed in cols [E:G]; input cells are cleared and locked for no User input. (not sure if this is what you want???)

    col [L] has dropdown.

    Formula in col [R] amended:
    This formula takes account of the number of entries in column [M] rather than number of entries in col [Q]
    Otherwise, for example, if a required dropdown selection in col [Q] was left 'empty', this may result in an 'eliminated' result showing in col [R]

    Formulas in col [Y] and col [Z] updated from your original posted file.

    The vba coding could be improved and be more efficient, but is left as is to show you what can be done.

    zeddy
    Attached Files Attached Files

  12. #11
    New Lounger
    Join Date
    May 2016
    Posts
    8
    Thanks
    0
    Thanked 1 Time in 1 Post
    Zeddy
    You are a star. A guru amongst guru's - Have fun off grid...Always makes me wonder what people do off grid...
    Tinfoil hats maybe?
    The Truth is out there!
    a significant help, you are

    Cheers Mate

  13. #12
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,721
    Thanks
    146
    Thanked 156 Times in 149 Posts
    He's probably mourning the relegation of his beloved Newcazzle United
    Talk is cheap because supply exceeds demand

  14. #13
    New Lounger
    Join Date
    May 2016
    Posts
    8
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by access-mdb View Post
    He's probably mourning the relegation of his beloved Newcazzle United
    Not a nice place to be...the bottom of the table...but with some creative macro's I think he could make it look like The Magpies are on top.

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi access-mdb

    The last game was a 5-1 win and we were down to 10 men.
    Now basically I don't like using cliches to be honest and avoid them like the plague
    but it goes without saying at the end of the day let's face it
    when you start talking about football you are opening up
    a pandoras box and you never know what trojan horses you
    might let out onto the level playing field where the grass is greener
    so I'll just literally grab the bull by the horns
    and no offence but every cloud has a silver lining and next
    season the team will be as fit as a fiddle and will take
    the tiger by the tail and by scoring outside the box and
    winning games left right and centre till the cows come home
    we can avoid the roller coaster of emotions and move forwards.

    zeddy

  16. #15
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,721
    Thanks
    146
    Thanked 156 Times in 149 Posts
    So you'll then be over the moon, especially if you score in every game early doors and it'll be Sunderland who are sick as parrots.
    Talk is cheap because supply exceeds demand

  17. The Following User Says Thank You to access-mdb For This Useful Post:

    zeddy (2016-05-27)

Page 1 of 2 12 LastLast

Posting Permissions

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