?
Solved

Excel difficult Challenge (dynamic named range to ignore hidden rows or filtered rows)

Posted on 2016-09-27
13
Medium Priority
?
324 Views
Last Modified: 2016-09-27
Hello,

i need help, is there possibility to create named range where it only encompasses the visible cells only?

thanks.
0
Comment
Question by:Flora
  • 6
  • 6
12 Comments
 
LVL 6

Author Comment

by:Flora
ID: 41817845
@D Patel

did you try the link before posting it here?

because i found that link by googling prior to post the question and it do not work at all.

see attached file. Column E do not return the visible cells from Column B, so that solution in mrexcel is not working at all.
Book.xlsb
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41818134
What you want to do isn't possible because a named range must be a range of contiguous cells, but if you describe how you would want to use this non-contiguous named range, then perhaps I could come up with a workaround.
0
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 41818586
After looking at your workbook I'm not sure what you want, because if all you want is for the cells in column E to be equal to what's in column B then all you need is a simple =B2 formula in E2 and copy down. However if you want to do it via VBA then this is one way. Add it to the sheet's code.
Private Sub Worksheet_Calculate()

Application.EnableEvents = False

Dim r As Range
For Each r In Range("B1:B" & UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible)
    r.Offset(0, 3) = r
Next
Application.EnableEvents = True
End Sub

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:Flora
ID: 41818651
Thanks Mr. Wolfe for removing Non-compliant answer.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41818670
I edited a typo in my last post.
0
 
LVL 6

Author Comment

by:Flora
ID: 41818675
Dear Martin,
thanks alot for your help as usual.

here is the real senario. please see attached workbook.  there is a formula in column A of sheet "Worksheet Names"  the formula is to list name of all worksheets of thisworkbook in column A.
it is all good and i have created a dynamic range named DynamicRangeSheets to encompass these sheets names in column A.  i pass the refer named ranges in my formulas to return these names as an array to inside my formula. it all works, but  becuase i have some sheets that contains wss and i want to exclude them from the named range hence i used filter in column A but my named range "DynamicRangeSheets"  still returns those hidden rows sheet names that contain wss.  i was wondering if there is a solution that my named range DynamicRangeSheets  excludes and ignores the hidden rows in the filter.  

if you select the named range and press F9 then you get ={"Sheet2";"Sheet3";"wssSheet4";"wssSheet5";"Sheet6";"Sheet7";"Sheet8";"Sheet9";"wwSheet10";"Sheet11";"Sheet12";"Sheet13";"Sheet14";"Sheet15";"Sheet16"}

i want the name range to return all sheets without the ones with wss
so the ideal needed result will be like ={"Sheet2";"Sheet3";"Sheet6";"Sheet7";"Sheet8";"Sheet9";"Sheet11";"Sheet12";"Sheet13";"Sheet14";"Sheet15";"Sheet16"}

this is just an example of the data, my workbook has many sheets that is why i needed a dynamic solution.

please let me know if you have further question and once again thank you very much for your professional help and support. it means the world to me and very much appreciated.
EE.xlsm
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41818708
Give me an example please of how you would use the (filtered) named range in a formula.
0
 
LVL 6

Author Comment

by:Flora
ID: 41818720
here is an example of formula.

please see the MyDYNAMICRANGE is holding the array of sheetnames

=SUMPRODUCT(SUMIFS(INDIRECT("'"&MyDYNAMICRANGE&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN(B1),4),"1","")&"$2:"&SUBSTITUTE(ADDRESS(1,COLUMN(B1),4),"1","")&"$40"),INDIRECT("'"&MyDYNAMICRANGE&"'!"&"$A$2:$A$40"),MainTemplate!$A2)*(T(INDIRECT("'"&MyDYNAMICRANGE&"'!"&"$A$1"))=MainTemplate!$B2))

Open in new window

0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41818736
The only named ranges in the workbook you posted are DynamicRangeSheets and Sheets, but that's OK.

Instead of a formula please describe what you would want to do with the dynamic range. What I'm looking for is something like "I'd like to sum up the values in A1 on all the sheets where the sheet name is visible".
0
 
LVL 6

Author Comment

by:Flora
ID: 41818763
sorry for confusion Martin.

DynamicRangeSheets  is the same as MyDYNAMICRANGE  i renamed DynamicRangeSheets to MyDYNAMICRANGE . as you can see in the example i attached.  i have sheet names in the column A and i put a filter. now the formula posted above do not honor the filtered of dynamic range column A
i need the MyDYNAMICRANGE to return only visible cells of column A inside the formula.

is this possible?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41818773
I'm sorry but you didn't answer my question which was...
Instead of a formula please describe what you would want to do with the dynamic range. What I'm looking for is something like "I'd like to sum up the values in A1 on all the sheets where the sheet name is visible".
0
 
LVL 6

Author Closing Comment

by:Flora
ID: 41818835
Martin, i think i figured it out using your code  of worksheet change event.

thanks alot
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question