Solved

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

Posted on 2016-09-27
13
137 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 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 47

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 47

Accepted Solution

by:
Martin Liss earned 500 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
Technology Partners: 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 47

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 47

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 47

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 47

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

759 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