Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Microsoft Office Excel cannot create or use the data range reference because it is too complex Error

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 31-12-2011
Member
 
Join Date: Dec 2011
Posts: 4
Microsoft Office Excel cannot create or use the data range reference because it is too complex Error

I am trying to copy/paste the filtered (subtotals) on my worksheet of about 17,000 rows. I do this every week. Unfortunately, I am getting the following error now: Microsoft Office Excel cannot create or use the data range reference because it is too complex. Try one or more of the following:
  • Use data that can be selected in one contiguous rectangle.
  • Use data from the same sheet.
Both of the above apply to my selection. Any help? Thanks.

Reply With Quote
  #2  
Old 31-12-2011
Member
 
Join Date: Jul 2011
Posts: 618
Re: Microsoft Office Excel cannot create or use the data range reference because it is too complex Error

When you filter data there is a limit to the number of discontinuous selections that Excel can handle. One solution is to sort the data so that like items are grouped adjacent. Then apply the filter and copy. Alternatively, you can select half of the filtered data and copy it, then select the other half and do it again. If this helps, please click the Yes button.
Reply With Quote
  #3  
Old 31-12-2011
Member
 
Join Date: Aug 2011
Posts: 564
Re: Microsoft Office Excel cannot create or use the data range reference because it is too complex Error

You mentioned that there's a limit to the number of discontinuous selections that Excel can handle, do you know what the number is? I have 65K rows and want to know the limitation so I know how many times I have to repeat the steps of copying discontinuous selections.
Reply With Quote
  #4  
Old 31-12-2011
Member
 
Join Date: Dec 2011
Posts: 34
Re: Microsoft Office Excel cannot create or use the data range reference because it is too complex Error

I am having the same issue (amongst a whole host of other issues with Excel 2007). However, my spreadsheet has about 130,000+ rows and about 130 columns, and growing. I can't sort the data, as my calculations then take forever to calculate, and my data needs to stay in the same sort order. It is very time-consuming to come and paste 25,000 rows at a time - is there a solution to this issue that Microsoft is working on?
Reply With Quote
  #5  
Old 31-12-2011
Member
 
Join Date: Dec 2011
Posts: 44
Re: Microsoft Office Excel cannot create or use the data range reference because it is too complex Error

Twist on the same error message. I have a spreadsheet using approximately 630k cells. It has been the same relative size for several months. This week after I apply one or two filters I get this error when simply trying to use find/replace. I think it is quite a contraddiction having so many line items in excel 2007 (compared to the 65k of excel 2003) and only 25k as limit. It takes me 45 min. to sort my data and I have tons. I will try to work on files using access.
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Microsoft Office Excel cannot create or use the data range reference because it is too complex Error"
Thread Thread Starter Forum Replies Last Post
Pivot table invalid data source reference error in Microsoft Excel CeLeStiA MS Office Support 2 25-02-2012 10:51 AM
Want some help to create cell reference in Microsoft Excel LuniA MS Office Support 2 23-02-2012 07:23 PM
Need help to create cross Reference table/Matrix in Microsoft Excel Eas!war MS Office Support 2 15-02-2012 05:01 PM
Getting Error message while creating Pivot table in Excel 2007: ?data source reference is not valid? Man-Than Windows Software 5 14-05-2011 10:49 AM
A Data Table in Microsoft Office Excel kattman Guides & Tutorials 7 29-04-2009 05:58 PM


All times are GMT +5.5. The time now is 05:16 AM.