Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I set cell order for columns on each page in Excel?
I am trying to create a multiple page pricelist in Excel. The problem I have
is that I need to have three columns with 20 rows in each column per page. I need the pricelist to be sequential on each column on each page. For example, the first column would list prices for parts 1 through 20 in order of the part number, then the second column would list the prices for parts 21 through 40, and the third column would list the prices for parts 41 through 60. Page two would start with parts 61-80, and so on. The problem I have is that if I try to insert a new cell for a new part, it shifts every cell in that column down on every page, which makes them out of order. Does anyone know how to set up the spreadsheet so that values are entered in x cell to x cell, then force the next value to move to column two, cell x through cell x, then to column three, cell x through cell x, then on to page two, column one, cell x through x, etc.? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I set cell order for columns on each page in Excel?
If you're not doing anything with that table except printing it, you could put
that table into MSWord. You can create columns (under Format|Columns), then inserting/deleting elements into that table will do exactly what you want. If you're really using that data in excel (although that layout sounds difficult to use), I would put the data on a separate worksheet. I'd a two columns: Part number and price. Then I'd update that list in place and use a macro to layout the data on a separate sheet. And I'd run that macro to recreate those "pretty table" worksheets whenever I updated that giant list. bilsons wrote: I am trying to create a multiple page pricelist in Excel. The problem I have is that I need to have three columns with 20 rows in each column per page. I need the pricelist to be sequential on each column on each page. For example, the first column would list prices for parts 1 through 20 in order of the part number, then the second column would list the prices for parts 21 through 40, and the third column would list the prices for parts 41 through 60. Page two would start with parts 61-80, and so on. The problem I have is that if I try to insert a new cell for a new part, it shifts every cell in that column down on every page, which makes them out of order. Does anyone know how to set up the spreadsheet so that values are entered in x cell to x cell, then force the next value to move to column two, cell x through cell x, then to column three, cell x through cell x, then on to page two, column one, cell x through x, etc.? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I set cell order for columns on each page in Excel?
In which column are the part numbers entered?
You must have a column for those in order to sort columns by part number. Post back then we can deal with ordering the 1-20, 21-40 etc. Gord Dibben MS Excel MVP On Wed, 10 Oct 2007 09:39:00 -0700, bilsons wrote: I am trying to create a multiple page pricelist in Excel. The problem I have is that I need to have three columns with 20 rows in each column per page. I need the pricelist to be sequential on each column on each page. For example, the first column would list prices for parts 1 through 20 in order of the part number, then the second column would list the prices for parts 21 through 40, and the third column would list the prices for parts 41 through 60. Page two would start with parts 61-80, and so on. The problem I have is that if I try to insert a new cell for a new part, it shifts every cell in that column down on every page, which makes them out of order. Does anyone know how to set up the spreadsheet so that values are entered in x cell to x cell, then force the next value to move to column two, cell x through cell x, then to column three, cell x through cell x, then on to page two, column one, cell x through x, etc.? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I set cell order for columns on each page in Excel?
Sorry, should have clarified. I need a total of 6 columns per page, not 3.
They would be grouped as the first two columns would contain one column for part number, one column for price. The other columns would be arranged in the same manner. IE: column 1 / column 2 / column 3 / column 4 / column 5 / column 6 part#1 / price part#10 / price part#19 / price part#2 / price part#11 / price part#20 / price part#3 / price part#12 / price part#21 / price part#4 / price part#13 / price part#22 / price part#5 / price part#14 / price part#23 / price part#6 / price part#15 / price part#24 / price part#7 / price part#16 / price part#25 / price part#8 / price part#17 / price part#26 / price part#9 / price part#18 / price part#27 / price "Gord Dibben" wrote: In which column are the part numbers entered? You must have a column for those in order to sort columns by part number. Post back then we can deal with ordering the 1-20, 21-40 etc. Gord Dibben MS Excel MVP On Wed, 10 Oct 2007 09:39:00 -0700, bilsons wrote: I am trying to create a multiple page pricelist in Excel. The problem I have is that I need to have three columns with 20 rows in each column per page. I need the pricelist to be sequential on each column on each page. For example, the first column would list prices for parts 1 through 20 in order of the part number, then the second column would list the prices for parts 21 through 40, and the third column would list the prices for parts 41 through 60. Page two would start with parts 61-80, and so on. The problem I have is that if I try to insert a new cell for a new part, it shifts every cell in that column down on every page, which makes them out of order. Does anyone know how to set up the spreadsheet so that values are entered in x cell to x cell, then force the next value to move to column two, cell x through cell x, then to column three, cell x through cell x, then on to page two, column one, cell x through x, etc.? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I set cell order for columns on each page in Excel?
I would enter/insert my new numbers and prices in any order in two columns on a
sheet named "Entry" These would then be sorted and copied to a sheet named "Copyto" which would have the format you want. Try this kludge for starters. Assumes "Entry" is activesheet when macro is run. Sub Move_Sets_Two_Column() Dim iSource As Long Dim iTarget As Long Set wksht = Worksheets("Copyto") Set wksht2 = Worksheets("Entry") With wksht .Cells.ClearContents End With wksht2.Range("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal iSource = 1 iTarget = 1 Do Cells(iSource, "A").Resize(20, 2).Copy _ Destination:=wksht.Cells(iTarget, "A") Cells(iSource + 20, "A").Resize(20, 2).Copy _ Destination:=wksht.Cells(iTarget, "C") Cells(iSource + 40, "A").Resize(20, 2).Copy _ Destination:=wksht.Cells(iTarget, "E") iSource = iSource + 60 iTarget = iTarget + 21 Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord On Thu, 11 Oct 2007 13:19:01 -0700, bilsons wrote: Sorry, should have clarified. I need a total of 6 columns per page, not 3. They would be grouped as the first two columns would contain one column for part number, one column for price. The other columns would be arranged in the same manner. IE: column 1 / column 2 / column 3 / column 4 / column 5 / column 6 part#1 / price part#10 / price part#19 / price part#2 / price part#11 / price part#20 / price part#3 / price part#12 / price part#21 / price part#4 / price part#13 / price part#22 / price part#5 / price part#14 / price part#23 / price part#6 / price part#15 / price part#24 / price part#7 / price part#16 / price part#25 / price part#8 / price part#17 / price part#26 / price part#9 / price part#18 / price part#27 / price "Gord Dibben" wrote: In which column are the part numbers entered? You must have a column for those in order to sort columns by part number. Post back then we can deal with ordering the 1-20, 21-40 etc. Gord Dibben MS Excel MVP On Wed, 10 Oct 2007 09:39:00 -0700, bilsons wrote: I am trying to create a multiple page pricelist in Excel. The problem I have is that I need to have three columns with 20 rows in each column per page. I need the pricelist to be sequential on each column on each page. For example, the first column would list prices for parts 1 through 20 in order of the part number, then the second column would list the prices for parts 21 through 40, and the third column would list the prices for parts 41 through 60. Page two would start with parts 61-80, and so on. The problem I have is that if I try to insert a new cell for a new part, it shifts every cell in that column down on every page, which makes them out of order. Does anyone know how to set up the spreadsheet so that values are entered in x cell to x cell, then force the next value to move to column two, cell x through cell x, then to column three, cell x through cell x, then on to page two, column one, cell x through x, etc.? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I set cell order for columns on each page in Excel?
Ok, let's pretend I'm an idiot and have never done this before :)
How in the world would I set this up? Is this creating a new macro? Can you walk me through this as you would a beginner? Thanks! "Gord Dibben" wrote: I would enter/insert my new numbers and prices in any order in two columns on a sheet named "Entry" These would then be sorted and copied to a sheet named "Copyto" which would have the format you want. Try this kludge for starters. Assumes "Entry" is activesheet when macro is run. Sub Move_Sets_Two_Column() Dim iSource As Long Dim iTarget As Long Set wksht = Worksheets("Copyto") Set wksht2 = Worksheets("Entry") With wksht .Cells.ClearContents End With wksht2.Range("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal iSource = 1 iTarget = 1 Do Cells(iSource, "A").Resize(20, 2).Copy _ Destination:=wksht.Cells(iTarget, "A") Cells(iSource + 20, "A").Resize(20, 2).Copy _ Destination:=wksht.Cells(iTarget, "C") Cells(iSource + 40, "A").Resize(20, 2).Copy _ Destination:=wksht.Cells(iTarget, "E") iSource = iSource + 60 iTarget = iTarget + 21 Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord On Thu, 11 Oct 2007 13:19:01 -0700, bilsons wrote: Sorry, should have clarified. I need a total of 6 columns per page, not 3. They would be grouped as the first two columns would contain one column for part number, one column for price. The other columns would be arranged in the same manner. IE: column 1 / column 2 / column 3 / column 4 / column 5 / column 6 part#1 / price part#10 / price part#19 / price part#2 / price part#11 / price part#20 / price part#3 / price part#12 / price part#21 / price part#4 / price part#13 / price part#22 / price part#5 / price part#14 / price part#23 / price part#6 / price part#15 / price part#24 / price part#7 / price part#16 / price part#25 / price part#8 / price part#17 / price part#26 / price part#9 / price part#18 / price part#27 / price "Gord Dibben" wrote: In which column are the part numbers entered? You must have a column for those in order to sort columns by part number. Post back then we can deal with ordering the 1-20, 21-40 etc. Gord Dibben MS Excel MVP On Wed, 10 Oct 2007 09:39:00 -0700, bilsons wrote: I am trying to create a multiple page pricelist in Excel. The problem I have is that I need to have three columns with 20 rows in each column per page. I need the pricelist to be sequential on each column on each page. For example, the first column would list prices for parts 1 through 20 in order of the part number, then the second column would list the prices for parts 21 through 40, and the third column would list the prices for parts 41 through 60. Page two would start with parts 61-80, and so on. The problem I have is that if I try to insert a new cell for a new part, it shifts every cell in that column down on every page, which makes them out of order. Does anyone know how to set up the spreadsheet so that values are entered in x cell to x cell, then force the next value to move to column two, cell x through cell x, then to column three, cell x through cell x, then on to page two, column one, cell x through x, etc.? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I set cell order for columns on each page in Excel?
Since you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm Or Ron de Bruin's site for where to enter macros and code. http://www.rondebruin.nl/code.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Mon, 15 Oct 2007 08:26:01 -0700, bilsons wrote: Ok, let's pretend I'm an idiot and have never done this before :) How in the world would I set this up? Is this creating a new macro? Can you walk me through this as you would a beginner? Thanks! "Gord Dibben" wrote: I would enter/insert my new numbers and prices in any order in two columns on a sheet named "Entry" These would then be sorted and copied to a sheet named "Copyto" which would have the format you want. Try this kludge for starters. Assumes "Entry" is activesheet when macro is run. Sub Move_Sets_Two_Column() Dim iSource As Long Dim iTarget As Long Set wksht = Worksheets("Copyto") Set wksht2 = Worksheets("Entry") With wksht .Cells.ClearContents End With wksht2.Range("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal iSource = 1 iTarget = 1 Do Cells(iSource, "A").Resize(20, 2).Copy _ Destination:=wksht.Cells(iTarget, "A") Cells(iSource + 20, "A").Resize(20, 2).Copy _ Destination:=wksht.Cells(iTarget, "C") Cells(iSource + 40, "A").Resize(20, 2).Copy _ Destination:=wksht.Cells(iTarget, "E") iSource = iSource + 60 iTarget = iTarget + 21 Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord On Thu, 11 Oct 2007 13:19:01 -0700, bilsons wrote: Sorry, should have clarified. I need a total of 6 columns per page, not 3. They would be grouped as the first two columns would contain one column for part number, one column for price. The other columns would be arranged in the same manner. IE: column 1 / column 2 / column 3 / column 4 / column 5 / column 6 part#1 / price part#10 / price part#19 / price part#2 / price part#11 / price part#20 / price part#3 / price part#12 / price part#21 / price part#4 / price part#13 / price part#22 / price part#5 / price part#14 / price part#23 / price part#6 / price part#15 / price part#24 / price part#7 / price part#16 / price part#25 / price part#8 / price part#17 / price part#26 / price part#9 / price part#18 / price part#27 / price "Gord Dibben" wrote: In which column are the part numbers entered? You must have a column for those in order to sort columns by part number. Post back then we can deal with ordering the 1-20, 21-40 etc. Gord Dibben MS Excel MVP On Wed, 10 Oct 2007 09:39:00 -0700, bilsons wrote: I am trying to create a multiple page pricelist in Excel. The problem I have is that I need to have three columns with 20 rows in each column per page. I need the pricelist to be sequential on each column on each page. For example, the first column would list prices for parts 1 through 20 in order of the part number, then the second column would list the prices for parts 21 through 40, and the third column would list the prices for parts 41 through 60. Page two would start with parts 61-80, and so on. The problem I have is that if I try to insert a new cell for a new part, it shifts every cell in that column down on every page, which makes them out of order. Does anyone know how to set up the spreadsheet so that values are entered in x cell to x cell, then force the next value to move to column two, cell x through cell x, then to column three, cell x through cell x, then on to page two, column one, cell x through x, etc.? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you make a two page purchase order? | New Users to Excel | |||
How do I reverse page order in excel workbook? | Excel Discussion (Misc queries) | |||
In Excel, how do you title a page across the top (across columns) | Excel Discussion (Misc queries) | |||
over, then down feature under page order in Excel | Excel Discussion (Misc queries) | |||
I want a purchase order that includes page number (if to be order. | New Users to Excel |