Results 1 to 6 of 6

Thread: If value is met hide/reveal named range

  1. #1

    If value is met hide/reveal named range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm looking for a formula that if the value "True" is met in cell i12 then my named range "Payer_Verify" will reveal. If this value is not met then it will be hidden until it is.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Assuming the named range is a single column, then something like:

    =IF($I$12=TRUE,INDEX(Payer_Verify,ROWS($A$1:$A1)),"")

    copied down

    else if it is multicolumn.

    =IF($I$12=TRUE,INDEX(Payer_Verify,ROWS($A$1:$A1),COLUMNS($A$1:A$1)),"")

    copied across and down


  3. #3
    It doesn't seem to be working. Its a multicolumn named range. The range I'm trying to reveal is A1017 if I12 is True. If its not true it stays hidden until then.

  4. #4
    A10: D17 is the range,

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    You tried?

    =IF($I$12=TRUE,INDEX($A$10:$D$17,ROWS($A$1:$A1),COLUMNS($A$1:A$1)),"")

    if still no, then maybe your TRUE is a text string rather than a boolean

    =IF($I$12="TRUE",INDEX($A$10:$D$17,ROWS($A$1:$A1),COLUMNS($A$1:A$1)),"")


  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Hi,
    Is A10 to D17 the named range you call Payer_Verify ?

    If not, then the formula given, =IF($I$12=TRUE,INDEX(Payer_Verify,ROWS($A$1:$A1),COLUMNS($A$1:A$1)),"")
    will reveal the contents of Payer_Verify if I12 is TRUE.
    So.... If you copy the formula into the cell range (say) AA10:AD17 then your named range is revealed there.
    If your wanting to hide Payer_Verify itself, as far as I know, you can't, unless your able to hide all the rows and columns it
    uses completely.
    You can hide text by setting it to the same colour as the background, which might be OK if you also protected the cells
    to stop them from being over-written unintentionally.

    HTH
    Last edited by Hercules1946; 2013-11-21 at 08:45 PM.

Posting Permissions

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