Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 153
Default Named Ranges anomaly(?)

I've got several named ranges. And I use those named ranges in some
conditional formatting. Initially, I had the whole columns selected
like below, but that was taking a lot of memory, and I've been having
issues with inserting/deleting lines taking a VERY long time to
complete. But at least the conditional formatting worked.

=QueryBuster!$B:$AO

To try and help with the memory issue, I tried a dynamic range like
below. Those were accepted by the system, but my conditional
formatting broke. It's like it's not seeing the range.
=OFFSET(QueryBuster!$B$1:$AO$1,0,0,COUNTA(QueryBus ter!$B:$B))

So I went to a static range that would at least encompass the largest
dataset that I could imagine, like below.
=QueryBuster!$B$1:$AO$5000

But after I saved and next updated the workbook, the conditional
formatting still didn't work, and when I went and looked at the named
ranges, they had changed to similar as below. All of the first
dimension were at 1048537.
=QueryBuster!$B1048537:$AO4960

So, three questions:
1) What might be the possible causes for the memory issue, and
how might I resolve that?
2) What is up with the Named Ranges?
3) What's the best way to go about this?

Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Named Ranges anomaly(?)

Firstly, your named ranges are column-absolute, row-relative. This can
result anomolies depending which cell was active when the name was
created.

My practice is to give the the header row a local scope, fully absolute
defined name...

Name: Sheet1!My_Hdr
RefersTo: =Sheet1!$B$1

...so my dynamic range has a 'base point'. Now I can make a dynamic
range as follows...

Name: Sheet1!MyData
RefersTo: =OFFSET(My_Hdr,1,0,COUNTA($B:$B)-1,COLUMN(AO1))

...so the data range begins in row2 because I subtract the header in the
sizing. You could also use a defined name ref for the rightmost column
as follows...

Name: Sheet1!LastCol
RefersTo: =Sheet1!$AO$1

...so your defined name is...

Name: Sheet1!MyData
RefersTo: =OFFSET(My_Hdr,1,0,COUNTA($B:$B)-1,COLUMN(LastCol))

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 153
Default Named Ranges anomaly(?)

Ah, I see what you are talking about and what I did wrong. I'll fix
that. Thank you!!

Frank

On Sat, 16 Apr 2016 01:50:43 -0400, GS wrote:

Firstly, your named ranges are column-absolute, row-relative. This can
result anomolies depending which cell was active when the name was
created.

My practice is to give the the header row a local scope, fully absolute
defined name...

Name: Sheet1!My_Hdr
RefersTo: =Sheet1!$B$1

..so my dynamic range has a 'base point'. Now I can make a dynamic
range as follows...

Name: Sheet1!MyData
RefersTo: =OFFSET(My_Hdr,1,0,COUNTA($B:$B)-1,COLUMN(AO1))

..so the data range begins in row2 because I subtract the header in the
sizing. You could also use a defined name ref for the rightmost column
as follows...

Name: Sheet1!LastCol
RefersTo: =Sheet1!$AO$1

..so your defined name is...

Name: Sheet1!MyData
RefersTo: =OFFSET(My_Hdr,1,0,COUNTA($B:$B)-1,COLUMN(LastCol))

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel named tables or dynimac named ranges? Håkan Björkström Excel Worksheet Functions 0 March 26th 15 07:49 PM
Named ranges and pasting formulas with named references Dude3966 Excel Programming 2 October 8th 08 04:15 PM
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... christian_spaceman Excel Programming 3 December 24th 07 02:15 PM
Copy data in named ranges to a newer version of the same template to identical ranges handstand Excel Programming 0 August 21st 06 03:51 PM
Like 123, allow named ranges, and print named ranges WP Excel Discussion (Misc queries) 1 April 8th 05 06:07 PM


All times are GMT +1. The time now is 08:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"