Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
KemS
 
Posts: n/a
Default Vlookup in large named range

I have a large named range (80 columns, 120 rows). Vlookup hangs (processor
pegged at 100%) now that I have created it. Would speed improve if I split
this large range into multiple smaller ones? I am keeping all vlookups
within the same worksheet based on advice from an older thread. I also tried
Index|Match but it was slower. The overall workbook is less than 1MB and is
linked to OLAP.

Thanks,
Kem
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default Vlookup in large named range

What's your formula? Is your data sorted?

--
Kind regards,

Niek Otten

"KemS" wrote in message
...
I have a large named range (80 columns, 120 rows). Vlookup hangs
(processor
pegged at 100%) now that I have created it. Would speed improve if I
split
this large range into multiple smaller ones? I am keeping all vlookups
within the same worksheet based on advice from an older thread. I also
tried
Index|Match but it was slower. The overall workbook is less than 1MB and
is
linked to OLAP.

Thanks,
Kem



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
KemS
 
Posts: n/a
Default Vlookup in large named range

Nick,
The data is not sorted. The formula: =VLOOKUP(Chart!$B$1,EXP,53,FALSE)
where Chart1B1 is a data validation drop down cell at the top of the
dashboard. The intent is to have the user only see the dashboard page which
has 17 graphs. They select the location from the drop down list and see the
data result.

I suspect from your answer that sorting the data may speed things up.

Kind regards,
Kem

"Niek Otten" wrote:

What's your formula? Is your data sorted?

--
Kind regards,

Niek Otten

"KemS" wrote in message
...
I have a large named range (80 columns, 120 rows). Vlookup hangs
(processor
pegged at 100%) now that I have created it. Would speed improve if I
split
this large range into multiple smaller ones? I am keeping all vlookups
within the same worksheet based on advice from an older thread. I also
tried
Index|Match but it was slower. The overall workbook is less than 1MB and
is
linked to OLAP.

Thanks,
Kem




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default Vlookup in large named range

I tried with 65536 VLOOKUPS, each looking up in 65536 cells; even then I
could hardly notice the calculation time.
Something else must be wrong.

Visit

www.decisionmodels.com

for advice

--
Kind regards,

Niek Otten

"KemS" wrote in message
...
Nick,
The data is not sorted. The formula: =VLOOKUP(Chart!$B$1,EXP,53,FALSE)
where Chart1B1 is a data validation drop down cell at the top of the
dashboard. The intent is to have the user only see the dashboard page
which
has 17 graphs. They select the location from the drop down list and see
the
data result.

I suspect from your answer that sorting the data may speed things up.

Kind regards,
Kem

"Niek Otten" wrote:

What's your formula? Is your data sorted?

--
Kind regards,

Niek Otten

"KemS" wrote in message
...
I have a large named range (80 columns, 120 rows). Vlookup hangs
(processor
pegged at 100%) now that I have created it. Would speed improve if I
split
this large range into multiple smaller ones? I am keeping all vlookups
within the same worksheet based on advice from an older thread. I also
tried
Index|Match but it was slower. The overall workbook is less than 1MB
and
is
linked to OLAP.

Thanks,
Kem






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
dynamic named range function MJB Excel Worksheet Functions 1 August 5th 05 05:56 AM
How do I edit a Named Range using macro's behmer Excel Worksheet Functions 2 July 26th 05 09:02 PM
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 05:21 AM
How to dynamically reference a dynamic named range paris3 Excel Worksheet Functions 4 June 24th 05 01:22 AM
Paste a named range to another range in Excel David Jean Excel Discussion (Misc queries) 2 April 13th 05 02:02 PM


All times are GMT +1. The time now is 02:22 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"