Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-09-27
13
Medium Priority
?
265 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 49

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 49

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
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.

 
LVL 6

Author Comment

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

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 49

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 49

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 49

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

704 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