Excel Report to Multiple Columns? 2

[MTBChik]

This may be pretty remedial, but I've been banging my head over it for several projects now and thought I'd see if anyone in here has any ideas.

We have a list of 400 properties in an Excel Spreadsheet. They only have three items of data associated with each property, Unit Name, Code1, ResCode. I would like to get all 400 properties on one or two sheets. In other words, I would like to be able to slice up the one long column of data into 3 columns per page, but ONLY when it prints.

Is this possible?

Thanks for the help!

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[SkipVought]

Could you maybe, post a sample of the data you are using along with the deired result?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[MTBChik]

Sample Data:

Vendor/Unit Vendor Code FRS Code
Aspen Ridge #3 AR3-ALL AR3
Bachman Village #5 B05-ALL BA5
Bachman Village #9 B09-ALL BA09
Bachman Village #14 B14-ALL BA14

What I'd Like to See
Is this repeated across the page three times. So the header row is repeated three times and then the data is listed below each header. (There's not enough room to type it in on this forum).

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[SkipVought]

I have a named range RowsPerPage that has the number of rows you want to have on each page

paste formula in A2 on sheet2

copy across and down

assuming that your source data is in sheet1 starting in A1...
[tt]
=INDEX(Sheet1!$A$2:$C$28,INT((COLUMN()-1)/3)*RowsPerPage+ROW()-1,MOD(COLUMN()-1,3)+1)[/tt]

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[SkipVought]

Actually, here's a more general solution for a different number of columns than 3.
[tt]
=INDEX(Sheet1!$A$2:$C$28,INT((COLUMN()-1)/COUNTA(Sheet1!$1:$1))*RowsPerPage+ROW()-1,MOD(COLUMN()-1,COUNTA(Sheet1!$1:$1))+1)
[/tt]

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[sawedoff]

You might want to change the $C$28 to $C$400 if you have 400 lines on your Sheet1.

Thanks for asking this one, I have always wondered it there was a way to do it, but never spent the time investigating.

Thanks Skip.

Sawedoff

 

[MTBChik]

Wheeehooo!

I have ALWAYS wanted to do this. Thanks so much!

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[SkipVought]

Actually, if I were setting this up to do it multiple times, I'd make the source data a DYNAMIC NAMED RANGE, using the OFFSET function in Insert/Name/Define...

Name the Range something like rSourceData
[tt]
=offset(Sheet1!$A$1,1,0,counta(Sheet1!$A:$A)-1),counta(sheet1!$1:$1))
[/tt]
then the formula becomes...
[tt]
=INDEX(rSourceData,INT((COLUMN()-1)/Columns(rSourceData))*RowsPerPage+ROW()-1,MOD(COLUMN()-1,Columns(rSourceData))+1)
[/tt]



Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[MTBChik]

Ooooo, Ok while we're on it I have a formatting question. The person who set this up should have set it up in a Word Doc, since there really is no data analysis that has to be done to it, but I don't want to freak out the sales people with new 'stuff', so I'm sticking with the Excel.

OK, here's the question. Some of the properties will allow discounting and are highlighted using yellow fill on Sheet1. How can I transfer the formatting to sheet2? I have added a column named Discount Count where A=10%, B=20%, etc. I just want the color to come to Sheet2.

I've tried to use conditional formatting (FormulaIs =IF(Sheet1!D2="A",,) then chosen (i.e.)Pattern as Yellow, but no formatting change occurs.

Any suggestions?

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.