1

Need to take 1 column of data and create 2 columns for maximizing the amount of data on each sheet.

Ive been working with offset and indirect formulas but not getting the expected results. Below are just a few examples of what ive tried and worked with and tried changing to get what i think i should be getting.

=IF(OFFSET(Sheet1!$A$1,(COLUMN()-1)*10+ROW()-1,0)="","",OFFSET(Sheet1!$A$1,(COLUMN()-1)*310+ROW()-1,0))

=OFFSET($A$1,ROW()*1-2+COLUMN(),0)&""

=INDIRECT("a"&ROW()*1-(2-COLUMN()))

So we have a need to create a excel template as dynamic as possible with as little printed pages as we can. Because the number of rows is unknown, we want to build the template in a fashion that allows for as little or as much data as we receive and need printed. So we have a need to fit 2 column sets per page, each set containing 10 rows each so we can have a total of 20 sets per page.

After finding offset and indirect, i started to work with that and see if i can create a formulas that would give me what we need..

What we are trying to see by using these formulas(if they are the correct ones to use) is the following:

Say we have 25 rows in our datasheet. We would like to see the following on our template.

Page 1

1     11

2     12

3     13

4     14

5     15

6     16

7     17

8     18

9     19

10    20


Page 2

21

22

23

24

25
cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
CubanGT
  • 351
  • 3
  • 11

1 Answers1

2

Do not use INDIRECT and OFFSET if possible (And I have only found rare times they are needed) as they are volatile functions. Index is the best option:

Page1 put this in the first cell, copy over one and down ten:

=INDEX(Sheet1!$A:$A,Row(1:1)+((column(A:A)-1)*10))

In each sequential sheet add a factor of 20:

=INDEX(Sheet1!$A:$A,Row(1:1)+((column(A:A)-1)*10)+(20*1))

Change the *1 to *2 for the third and so on.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Can this method be changed to be dynamic? Meaning i know we will only want a max of 20 per page, it can be less if we dont have enough to fill a page, but if we say 135 rows, will we have to do the counting ourselves and tweek the formula for every 20? – CubanGT Sep 26 '19 at 20:42
  • Yes, it would need to be manual as a formula cannot tell on which number sheet it is being placed. For that you would need vba. – Scott Craner Sep 26 '19 at 20:45
  • So i love your example and in its simple form, it works great.. How can i modify the formula to incorporate my current formulas in my spreadsheet? Here is what i have in my A1 cell =UPPER(Data!O2) would i place that into the formula =INDEX(Sheet1!$A:$A,ROW(1:1)+((COLUMN(A:A)-1)*10)) in place of the "Sheet1!$A:$A" – CubanGT Oct 09 '19 at 14:26
  • No, you would wrap the whole formula in UPPER(...) – Scott Craner Oct 09 '19 at 14:34
  • So i modified the formula for column 1 in my sheet to this =INDEX(Data!$A2,ROW($1:$1)+((COLUMN(A:A)-1)*10)) and this works for the first set of 10, but when i try to copy the formula in column 2 with the continuation formula it gives a #REF error – CubanGT Oct 09 '19 at 14:43
  • This is my 2nd column formula that gets the error =UPPER(INDEX(Data!$A2,ROW($1:$1)+((COLUMN(B:B)-1)*10))) – CubanGT Oct 09 '19 at 14:45
  • you modified the formula incorrectly: `UPPER(Data!$A:$A,ROW(1:1)+((COLUMN(A:A)-1)*10)) ` copy over and down. – Scott Craner Oct 09 '19 at 17:21
  • So just to make sure, im building a new template with this formula and see if i can use it as i think it will work.. So i just need to use the same formula in each column i want to wrap around ONLY changing the cell that contains the data i want to display in that new place on the template. – CubanGT Oct 09 '19 at 18:12
  • I think at this point the best thing to do is ask a new question so you can show what you have and what you want and what you tried, I am getting confused with just comments. – Scott Craner Oct 09 '19 at 18:20
  • Ill open another question soon, i was able to start setting up the template with your last example and seems to be working, just need to format things.. if there was a way to post a picture of how i need it and how its setup i would, thank you Scott.. – CubanGT Oct 09 '19 at 20:43