• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Numbering of Cells/Table Creation

Castetter

New Member
I am in need of a way to dynamically build a table based on the a few parameters that I enter in my worksheet. For example, say that I have a cell that allows me to enter the number of names per team and another cell that asks how many teams...so I put in [5] names per team and [3] teams. I would like to be able to dynamically create a table that in the first column contains the team number (1 through 3) and the second column contain the number of the name (1 through 5) like so:


1 1

1 2

1 3

1 4

1 5

2 1

2 2

2 3

2 4

2 5

3 1

3 2

3 3

3 4

3 5


But if I change the number of names per team to [10] it expands the second column to allow for 10 values. I can do this using VBA but I was wondering if there was a trick using formulas.


BTW, I am using 2003 for now. Thank you in advance for your help.
 

keymaster

New Member
You can easily do this using formulas.


Assuming no. of teams is in cell A1, and member size is in cell A2. Now, lets generate the table in the column B and C.


In B1 write =if(rows($b$1:b1)<=$A$1*$A$2,int(rows($b$1:b1)/$A$2),"")

and in C1 write =if(rows($b$1:b1)<=$A$1*$A$2,mod(rows($b$1:b1)/$A$2),"")


now copy the formulas over the next few hundred rows in column B and C.


When you edit A1 or A2, the table will be regenerated.
 

Castetter

New Member
Chandoo,


So close...but a few questions. First off, when I paste in your formulas as is I get a "too few arguments" for the formula that goes in C1...so I don't completely understand the formula but I put this in it's place "=IF(ROWS($B$1:B1)<=$A$1*$A$2,MOD(ROWS($B$1:B1),$A$2),"")" although I don't think that it is right.


So here is what I have in my spreadsheet:

in A1 I have set it to a range called "Teams" and the value is 2

in A2 I have set it to a range called "Players" and the value is 2

in B1 I have the following formula: "=IF(ROWS($B$1:B1)<=Teams*Players,INT(ROWS($B$1:B1)/Players),"")"

in C1 I have the following formula: "=IF(ROWS($B$1:B1)<=Teams*Players,MOD(ROWS($B$1:B1),Players),"")"


Here is the output:

0 1

1 0

1 1

2 0


I was looking for:

1 1

1 2

2 1

2 2


I think that the 0 in the first column is because INT returns the next lower whole integer (1/2=0.5 which goes to 0) and the 0 in the second column is calculated because the mod function has no remainder (2,2)...but maybe not? I have not used those functions before...so maybe I am completely off base here. Anyway, it's close...very close. Any suggestions would greatly be appreciated.


Thank you for your help.

Castetter
 
Top