Problem with collumns and pages...

Cactus

Welcome to the Mr Excel board!

See if this is any help:
http://www.mrexcel.com/board2/viewtopic.php?t=178956

Here is a similar way but with more efficient formulas.

Original list is in columns A & B (but the sheet can easily be modified for 3, 4 etc columns to be re-aligned for printing).

1. C2: =COUNTA($A1:B1) however this cell could have the number of columns entered manually.
2. C4: =MAX(D:D)
3. Set up column D and the rest of row 1 for the grid layout you want. Clearly the numbers in row 1 need to be a multiple of the C2 value.
4. Set up grid headings in row 2.
5. Formula in E3 (copied across and down) is:
=IF(INDEX($A:$B,1+$C$4*INT((E$1-1)/$C$2)+$D3,1+MOD(E$1-1,$C$2))="","",INDEX($A:$B,1+$C$4*INT((E$1-1)/$C$2)+$D3,1+MOD(E$1-1,$C$2)))
6. Set print area to E2:H11 (adjust range to suit your data)

thanks so much for your help, guys. appreciate it. i'll try both and see which one works best for me.

I can't quite work out the formula. I have two columns of info, A and B and then many rows, beginning at Row 1. I'm confused with the C2 and C4 references (figured i should put those formulas in C2 and C4 and did), but this: "Set up column D and the rest of row 1 for the grid layout you want. Clearly the numbers in row 1 need to be a multiple of the C2 value." i don't know what that means. can you dumb it down a little for me please. Thank you,

I can't quite work out the formula. I have two columns of info, A and B and then many rows, beginning at Row 1. I'm confused with the C2 and C4 references (figured i should put those formulas in C2 and C4 and did), but this: "Set up column D and the rest of row 1 for the grid layout you want. Clearly the numbers in row 1 need to be a multiple of the C2 value." i don't know what that means. can you dumb it down a little for me please. Thank you,

Click to expand...

OK, let's try.

You have two columns in columns A and B, just like in my sample above. Just to make it a bit easier to follow from the example above, I suggest that you put in a new row 1 above your data (select A1 then Insert|Rows), so that your data starts in row 2 like my sample.

Put a 2 in C2 and the formula suggested in C4.

Let's suppose you want six columns on each page. So in E1 across to J1, put 1,2,3,4,5,6. I don't know how many rows you have but in D3 put a 1 and in D4 put a 2. Now select D1:D2 and drag the Fill handle (the little black square at the bottom right of the selection) down some distance.

Now copy this formula
=IF(INDEX($A:$B,1+$C$4*INT((E$1-1)/$C$2)+$D3,1+MOD(E$1-1,$C$2))="","",INDEX($A:$B,1+$C$4*INT((E$1-1)/$C$2)+$D3,1+MOD(E$1-1,$C$2)))
and paste it into E3.

Select E3 and drag the Fill Handle across to J3. Now select E3:J3 and drag the Fill Handle down as far as you put numbers in column D.

If this does not bring all your data from columns A and B into columns E:J then drag the numbers down further in column D and the formulas down in columns E:J

Set your print area (File|Page Setup...|Sheet tab) to E3:Jxx where xx is the last row of data showing in columns E:J

See how that goes.

that didn't work and i did exactly how you said. however i think i might have an idea of what when wrong. In this instruction:

Let's suppose you want six columns on each page. So in E1 across to J1, put 1,2,3,4,5,6. I don't know how many rows you have but in D3 put a 1 and in D4 put a 2. Now select D1:D2 and drag the Fill handle.

When I select and drag/filled D1:D2 down, that meant I overwrote what you just had me put in D3 and D4 (1 and 2 respectively). you can't have had me done that for nothing. so perhaps i'm supposed to select / drag and fill somewhere else????

that didn't work and i did exactly how you said. however i think i might have an idea of what when wrong. In this instruction:

Let's suppose you want six columns on each page. So in E1 across to J1, put 1,2,3,4,5,6. I don't know how many rows you have but in D3 put a 1 and in D4 put a 2. Now select D1:D2 and drag the Fill handle.

When I select and drag/filled D1:D2 down, that meant I overwrote what you just had me put in D3 and D4 (1 and 2 respectively). you can't have had me done that for nothing. so perhaps i'm supposed to select / drag and fill somewhere else????

Click to expand...

Sorry, my mistake

Should have been:
... in D3 put a 1 and in D4 put a 2. Now select D3:D4 and drag the Fill handle.

At the end of the day it should look like the sample I posted - apart from there being six columns instead of four.

i redid it and it's still not working for me. All the cells from E3:J476 say: #DIV/0!

Roni

I sent you a PM regarding this. Did you get it? Or have you resolved the issue satisfactorily?