Excel 2007 Auto Fill Row of Cells

  • wpas
  • Proficient
  • Proficient
  • User avatar
  • Posts: 322
  • Loc: Canada

Post 3+ Months Ago

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
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Alkatr0z
  • Mastermind
  • Mastermind
  • Alkatr0z
  • Posts: 1883
  • Loc: Adelaide, Australia

Post 3+ Months Ago

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
  • Proficient
  • Proficient
  • User avatar
  • Posts: 322
  • Loc: Canada

Post 3+ Months Ago

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
  • wpas
  • Proficient
  • Proficient
  • User avatar
  • Posts: 322
  • Loc: Canada

Post 3+ Months Ago

I took your idea and used COLUMN instead as follows:

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


Worked great

Thanks

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 101 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
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.