Solved

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

Posted on 2016-09-27
13
221 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 48

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 48

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 6

Author Comment

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

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 48

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 48

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 48

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

636 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