# Excel 2007 Auto Fill Row of Cells

• wpas
• Joined: Jul 12, 2010
• Posts: 214
• Status: Offline

July 23rd, 2012, 8:12 am

Hi All
Hopefully someone can help me.

I have a situation as follows:

Row of Cells A1:AF1 (32 cells)

I calculate two values such as:
A2 = 23
A3 = 9
Both will always add up to 32

What I want to do is the following:

1) Using the value in A2, 23, fill the first 23 cells with digit 1
2) Using the value in A3, 9, fill the last 9 cells with digit 0

Of course, A2 and A3 can changes values, such as:
A2 = 28
A3 = 4

In this case then, I want the following:

) Using the value in A2, 28, fill the first 28 cells with digit 1
2) Using the value in A3, 4, fill the last 4 cells with digit 0

I want to use a function that I can insert into the spreadsheet so it does it automatically.
If a VBA is needed, I have no experience, which is why a spreadsheet function or formula would be much easier for me to understand and work with.

Thanks
http://www.schembrionics.com
The Ultimate Solutions Center
• Anonymous
• Bot
• Joined: 25 Feb 2008
• Posts: ?
• Loc: Ozzuland
• Status: Online

July 23rd, 2012, 8:12 am

• Alkatr0z
• Mastermind
• Joined: Feb 08, 2004
• Posts: 1883
• Status: Offline

July 31st, 2012, 12:15 am

Hi wpas,

You could try something like this:
Code: [ Select ]
=IF(ROW()<=\$A\$1,1,IF(ROW()<=\$A\$1+\$A\$2,0,2))

Replace \$A\$1 with your first "row count" e.g. \$A\$2 in your example
Replace \$A\$2 with your second "row count" e.g. \$A\$3 in your example

Then you need to put the formula in the same spreadsheet and drag it down to fill the other 32 cells that you want to be affected by the values in the two reference cells.

The 2 at the end can be changed to anything else, it only appears after there are more than 32 rows that you've dragged it down for or if the two reference cells don't add up to 32.

This is made to only go down the page in a single column, starting at Row 1. If you start at a different Row then you need to change ROW() to be ROW() - # of rows otherwise it won't work.
• wpas
• Joined: Jul 12, 2010
• Posts: 214
• Status: Offline

July 31st, 2012, 8:19 am

hi Alk

What I want is in a single row, all 32 colums to fill
I guess I did not quite say it properly
I do not want to go down rows in a single column
http://www.schembrionics.com
The Ultimate Solutions Center
• wpas
• Joined: Jul 12, 2010
• Posts: 214
• Status: Offline

July 31st, 2012, 9:06 am

Code: [ Select ]
=IF(COLUMN()<=\$A\$1,1,IF(COLUMN()<=\$A\$1+\$B\$1,0,2))

Worked great

Thanks
http://www.schembrionics.com
The Ultimate Solutions Center

## Post Information

• Total Posts in this topic: 4 posts
• Users browsing this forum: No registered users and 187 guests
• You cannot post new topics in this forum
• You cannot reply to topics in this forum
• You cannot edit your posts in this forum
• You cannot delete your posts in this forum
• You cannot post attachments in this forum

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.