Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Too many rows and columns Results

I have made a report in pivot table.

My Headings

Sales A Sales B Sales C
Region Name
Location Code
Location Code
Category
Location
Cluster Head

After made the pivot i started the grouping but is ther any limitation for excel for this? It is showing the below message.

"Microsoft Excel cannot make this change because there are too many row or column items. Drag at least one row or column field off the PivotTable, or to page position. Alternatively, right click a field, and then click Hide or Hide Levels on the shortcut

How to over come this and do the grouping. Is this limitation of Excel

I am using Office 2000

Regds
Prakash

I have a data set from which I am making a pivot table. I wish the pivot
table to display the two separate columns from the database as "data" with
subtotals. Yet when I place the two columns into the data section of the
pivot table, I get an error: too many rows or columns in pivot table.

I can easily put one of the columns into the pivot table column section and
have the other as the data for the pivot table, but then I cannot (to my
knowledge) get a subtotal from the each of the two columns, but only from the
one listed explicitly as data.

Any ideas?

Is there a way around the error message that I am getting?

"Microsoft Excel cannot make this change because there are too many row or column
items. Drag at least one row or column field off the pivottable, or to the page
position. Alternatively, right click a field, and then click hide or hide levels on the shortcut menu."

I have data consisting of 3818 rows and 7 columns. I need all of the data, so I can't cut any of it out.

Is there a way to get MS to accept the amount of data and give me my Pivot Table???

Thanks,
Jason

How can I delete all empty rows and columns to have just the cells that
contain data? I need this because, when I save the data an acess file and
then to dbf, I end up getting too many rows and columns.

Friends,

The data I am accessing for my pivot table would comprise only 500 rows if
Excel would let me put the 5 rows into the Row area, but I get the following
error message:

"Microsoft Excel cannot make this change because there are too many row or
column items. Drag at least one row or column field off the Pivot Table, or
to the page position. Alternatively, right click a field, and then click Hide
or Hide Levels on the shortcut menu."

Why is Excel giving this error when I am no where near the row or column
limit of Excel?

Thanks so much for your help ...

bill morgan

Hello, I have an issue with my pivot-table.

I have two right-hand-side columns and one left-hand side one

Like this: Customer..Ordernumber.....................Total renevue

I would like to get the articles that the customer bought as well when I
click the ordernumber, but Windows tells me (translated from Swedish) that I
have too many rows- or columnelemnents, and that I have to withdraw at least
one row- or column element.

Is there any way to get around this problem?

Friends,

The data I am accessing for my pivot table would comprise only 500 rows if
Excel would let me put the 5 rows into the Row area, but I get the following
error message:

"Microsoft Excel cannot make this change because there are too many row or
column items. Drag at least one row or column field off the Pivot Table, or
to the page position. Alternatively, right click a field, and then click Hide
or Hide Levels on the shortcut menu."

Why is Excel giving this error when I am no where near the row or column
limit of Excel?

Thanks so much for your help ...

bill morgan

Hello, I have an issue with my pivot-table.

I have two right-hand-side columns and one left-hand side one

Like this: Customer..Ordernumber.....................Total renevue

I would like to get the articles that the customer bought as well when I
click the ordernumber, but Windows tells me (translated from Swedish) that I
have too many rows- or columnelemnents, and that I have to withdraw at least
one row- or column element.

Is there any way to get around this problem?

Apparently someone highlighted the whole worksheet when creating cell outlines, so we have HUNDREDS of pages of just blank cells when printing (we caught it before too many sheets were printed.). I ended up just copying the context of the worksheet onto another worksheet in the same book. I was just wondering how I could delete the cells without doing this step.

Below is my code for importing data from a text file into an excel sheet. It works fine except that it adds 2 additional rows in addition to the row of data which I need. I cannot figure out what is going on. I need some fresh eyes. Help is greatly appreciated. TY in advance.

Dim DirText As String
Dim DefCodeStarts As Integer
DefCodeStarts = 28
Dim Count_DefectTypes As Integer
Dim TargetWB As String
Dim TargetWS As String
If Left(ActiveWorkbook.Name, 3) = "IMT" Then
'WBname = ActiveWorkbook.Name
'TargetWB = "IMT1-(F32)_STRFC-PINT.xls"
TargetWB = ActiveWorkbook.Name
TargetWS = ActiveSheet.Name
Dim TargetType As String

TargetType = Right((Mid(TargetWS, 1, (Len(TargetWS) - 6 - 1))), 3)

Dim CurrentLotCounter As Integer
Dim DataWB As String
Dim DataWS As String
Dim DataLotCounter As Integer
Dim PlaceLotCounter As Integer

zzz = MsgBox("Would you like to overrite exsiting data? Press yes to overwire or press no to append data to the end", vbYesNo, "Import Data")

DirText = Application.GetOpenFilename("Report File (*.rpt), *.rpt")
If DirText False Then
Workbooks.OpenText Filename:=DirText, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False

'Establish name of data ws & wb
found = 0
For x = Len(DirText) To 1 Step -1
y = Mid(DirText, x, 1)
If Mid(DirText, x, 1) = "" And found = 0 Then
found = 1
stopper = x - 1
End If
Next
DataWB = Right(DirText, Len(DirText) - stopper - 1)
DataWS = Mid(DataWB, 1, Len(DataWB) - 4)
Windows(DataWB).Visible = False

'All rows and columns must not be hidden
Workbooks(TargetWB).Worksheets(TargetWS).Cells.EntireColumn.Hidden = False
Workbooks(TargetWB).Worksheets(TargetWS).Cells.EntireRow.Hidden = False

'Count existing lots
CurrentLotCounter = 0
Do
CurrentLotCounter = CurrentLotCounter + 1
Loop Until Workbooks(TargetWB).Worksheets(TargetWS).Cells(CurrentLotCounter + 7, 2).Interior.ColorIndex 34
'Find where the defect codes are
DefCodeStarts = 0
Do
DefCodeStarts = DefCodeStarts + 1
Loop Until Workbooks(TargetWB).Worksheets(TargetWS).Cells(2, DefCodeStarts).Value = "Code"

'Find number of defect codes
Count_DefectTypes = -1
Do
Count_DefectTypes = Count_DefectTypes + 1
Loop Until Workbooks(TargetWB).Worksheets(TargetWS).Cells(2, Count_DefectTypes + 7).Value = ""
Count_DefectTypes = Count_DefectTypes - 1
If zzz = 6 Then
'Overwright exisiting data
For R = 8 To 8 + CurrentLotCounter
Workbooks(TargetWB).Worksheets(TargetWS).Range(Cells(R, 1), Cells(R, Count_DefectTypes + 7)).Clear
'For C = 1 To Count_DefectTypes+7
' Workbooks(TargetWB).Worksheets(TargetWS).Cells(R, C).Clear
'
'Next C
Next R
CurrentLotCounter = 1
End If
'Get number of lots from data ws
DataLotCounter = 0
Do
DataLotCounter = DataLotCounter + 1

datacol = FindColumn("Lot ID", 1, DataWB, DataWS)
'X chart column
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 1).Value = "X"
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 1).Font.Name = "Arial"
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 1).Font.Size = 8
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 1).HorizontalAlignment = xlCenter
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 1).VerticalAlignment = xlCenter
'Get LotID
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 2).Value = Workbooks(DataWB).Worksheets(DataWS).Cells(DataLotCounter + 1, datacol).Value
Call FormatCell(TargetWB, TargetWS, 7 + CurrentLotCounter, 2)
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 2).Interior.ColorIndex = 34
'format kind
Call FormatCell(TargetWB, TargetWS, 7 + CurrentLotCounter, 3)
'Get Inspection Time
datacol = FindColumn("Inspection Time", 1, DataWB, DataWS)
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 4).Value = Workbooks(DataWB).Worksheets(DataWS).Cells(DataLotCounter + 1, datacol).Value
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 4).Interior.ColorIndex = 34
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 4).NumberFormat = "DD-MMM HH:MM"
Call FormatCell(TargetWB, TargetWS, 7 + CurrentLotCounter, 4)
'format pass/fail
Call FormatCell(TargetWB, TargetWS, 7 + CurrentLotCounter, 5)
'Get EDI or DD
If TargetType = "EDI" Then
datacol = FindColumn("F32", 1, DataWB, DataWS)
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 6).NumberFormat = "0.0"
ElseIf TargetType = "_DD" Then
datacol = FindColumn("Defect Density", 1, DataWB, DataWS)
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 6).NumberFormat = "0.000"
End If
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 6).Value = Workbooks(DataWB).Worksheets(DataWS).Cells(DataLotCounter + 1, datacol).Value
Call FormatCond(TargetWB, TargetWS, 7 + CurrentLotCounter, 6, 3, 6)
Call FormatCell(TargetWB, TargetWS, 7 + CurrentLotCounter, 6)
'Get defect types
For dt = 1 To Count_DefectTypes
CurrentDT = Workbooks(TargetWB).Worksheets(TargetWS).Cells(dt + 2, DefCodeStarts + 2).Value
datacol = FindColumn(CurrentDT, 1, DataWB, DataWS)
If datacol = 217 Then
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 6 + dt).Value = 0
Else
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 6 + dt).Value = Workbooks(DataWB).Worksheets(DataWS).Cells(DataLotCounter + 1, datacol).Value
End If
Call FormatCond(TargetWB, TargetWS, 7 + CurrentLotCounter, 6 + dt, 3, 6 + dt)
Call FormatCell(TargetWB, TargetWS, 7 + CurrentLotCounter, 6 + dt)
If TargetType = "EDI" Then
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 6 + dt).NumberFormat = "0.0"
ElseIf TargetType = "_DD" Then
Workbooks(TargetWB).Worksheets(TargetWS).Cells(7 + CurrentLotCounter, 6 + dt).NumberFormat = "0.000"
End If
Next
CurrentLotCounter = CurrentLotCounter + 1
Loop Until Workbooks(DataWB).Worksheets(DataWS).Cells(DataLotCounter, 1).Value = ""

Workbooks(DataWB).Close SaveChanges:=False
End If
Else
MsgBox ("This macro is not running on the correct workbook")
End If
End Sub

Hello,

Can someone show me the code which would allow me to search through a number
of rows and columns, to identify a criteria. Each time the criteria is
found, 1 is added to a variable. When the variable reaches a set number, the
search moves to a different column.

This would allow me to allocate people to jobs according to their first,
second, third choice etc, without resourcing too many people to a job.

can you help? I have very little experience, but am usually able to see
what is happening in the code and expend on it to take care of more elements.

kind regards,

Matt

Excel 2007:
I have two workbooks, both are .xls's. I'm trying to copy a worksheet from the first workbook in to the second workbook. When I right click the worksheet and select "Move or Copy", I get an error saying that the number of columns and rows in the first workbook exceed the number of columns and rows in the second workbook.

The first workbook has the increased number of columns and rows, whereas the second workbook is limited to 256 / 65k respectively. No matter what file format I save either workbook as, the number of columns and rows are unaffected.

How can I copy a worksheet from a workbook that has too many row and columns (even though I'm only using a few of each) in to a workbook that is limited to the pre-Excel 2007 amounts? Please note that I can't simply copy/paste the data as I need to retain the page setup from the first workbook as well.

I can copy from the second workbook to the first, but it has several worksheets which take 5 times the number of clicks to copy over. It's much easier to go from the first workbook to the second.

Thanks.

Hi All;
I am a bit new to this software and thanks for replying.

There is a workbook with many values mostly string with more than 12 characters.I wish to know if its possible to get the row and coloumn number of the searched value without using loops.

I used two for loops to match the string and get the row and coloum number.But when there are many values in the sheet,it takes too much time.So Is there any way to make the time shorter by a different method?or if its not possible,a faster way of searching with loops?There is only one occurance in the sheet and the value is only in one sheet.Thanks a lot and have a nice day

Hi All,

I have a sheet that I want to print in 2 pages ( too many columns). I have columns A to D and rows 1 to 9 to repeat on the second page. The title in cloumn A is too big that it cuts off in the second page.

How do I fix this?

Thank you for your help.

You either have to put the data in more than one sheet or wait until Excel
2007 is coming out (you can download a beta2 version right now, do a web
search for download beta 2 office 2007), 2007 has 1 million rows and over
16000 columns

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com

"Bright Spark" > wrote in message
...
>I have a mailing list in .txt format that I need to open in Excel in
> order to clean up and sort, etc. The problem I am having is that the
> list is 76,007 records long and Excel will not allow me to have that
> many - I get the message "File not completely loaded" (or something
> similar).
>
> I have done some searching and I have discovered that only 65,536 rows
> can be in one worksheet (that is exactly how many it will load) but
> there does seem to be some way to increase that number as long as the
> final number is below 98,304 rows.
>
> I am a total "newbie" as far as Excel is concerned and purely
> hunt-and-peck my way through using it, learning as I go, but I cannot
> figure out how I can end up with my full 76,007 records in just one
> worksheet?
>
> I would appreciate some instruction on this if anyone can help or
> point me in the right direction.
>
> Thanks very much.
>
> BrightSpark
>

My source data table is 74 columns and 1,297 rows. I am trying to create a
pivot table with multiple columns from the source data as the "data" in the
pivot table. When I try to do this I get an error that says...
"Excel cannot make this change because there are too many row or column
items...."

Is there a limit on the size?

Any suggestions?
Thanks!
Jenni

I have been importing text file into Excel 2007 by using Open command. This
works fine, but when I try to copy the sheet to any other workbook, I am
getting a message to say there are too many rows and columns to carry out the
operation. The text file has several hundred rows, but when opened in Excel,
the workbook has 1048576 rows instead of the standard 65536. This means
that it will not copy to a workbook with the standard number. Is there a way
round this? I do realise I can copy and paste, but would like to know the
answer to the copy sheet question if possible.

Excel 2007 compatability mode for 2003- I have a list tab with text values in column C "ID" and a tab with a pivot table. The pivot table had 500,000 rows of underlying data. Based on the cell values of "ID" in column C(in each row), I would like to be able to see the underlying data of the pivot table for only the rows of underlying data where the uderlying data field "ID" matches the "ID" value in column C.

Note that the pivot table can not use the field "ID" becuase there are too many values and it can not represent those.

Also note that I need to be backward compatable to 2003.

I tried a hyperlink but am not sure how to reference the underlying data or how to programatically spawn the results.

Hi all,

I'm working on a spreadsheet and have hit upon a problem. Due to an extension in the range I am calculating my nested formula now exceeds the magical 7 limit. I only actually need to add on more range to calculate, but cannot work out what to do.

The formula I'm using is below. I basically just need to do the same calculation for columns BT and BU:

=IF('Resource Calcs'!B2="","",IF('Resource Calcs'!$BF2='Resource By Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BG2,IF('Resource Calcs'!$BH2='Resource By Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BI2,IF('Resource Calcs'!$BJ2='Resource By Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BK2,IF('Resource Calcs'!$BL2='Resource By Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BM2,IF('Resource Calcs'!$BN2='Resource By Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BO2,IF('Resource Calcs'!$BP2='Resource By Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BQ2, IF('Resource Calcs'!$BR2='Resource By Div'!$A$1,'Resource Calcs'!B2*'Resource Calcs'!$BS2, 0))))))))

I've been advised that I could split the formula across two cells, the only problem being that I then copy the formula across a massive range of around 2000 cells, and it will take quite some time to add new rows and columns...looking for a simpler solution!

Any ideas?

Thanks!

SamuelT

I am a tipped worker, and have created a spreadsheet to track my daily, weekly, and yearly earnings.

I have a sub-total row for each week, and a cumulative total row at the bottom of the spreadsheet, tracking my hours, target earnings, actual earnings, etc. Here is one of the formulas from the Cumulative Totals row:

=SUM(D32, D39, D47, D54, D61:D68, D75, D82, D89, D96, D103, D110, D117, D125, D132, D139, D146, D153, D160, D167, D174, D181, D188, D195, D202, D209, D216, D223, D231)

Every couple of weeks, I go into the cumulative totals row and enter the last few weeks weekly total cells. Tonight, I tried to enter D238, D245, and d252 into the formula. When i did, I got an error message saying:

"You've entered too many arguments for this function.

To get help with entering arguments for this function, click OK to close this message. Then, on the formula bar, click the equal sign button (located to the left of the equal sign in your formula)."

The thing is, there is no equal sign on the formula bar that i can see. the button located to the left of the equal sign in my formula is the fx button, and pressing it offers me no help.

Obviously, this formula is going to be much longer by the end of the year, there are 52 weeks in the year. What is the fix for this?

Also, what I do now is keep a blank week's worth of rows and columns at the bottom of the workbook (above the Cumulative Totals row). Each week, I copy that "module" and insert it below the last week, filling in the actual dates I work that week. Every month, or so, I then go into the formulas on the Cumulative Totals row and add in the weekly sub-total cells. Is there a better way to do this?


No luck finding an answer? You could always try Google.