Fill series - very urgent

thiag.siva

New Member
Joined
Aug 14, 2009
Messages
5
Hi

I want to fill series in this format in excel 2007

<table style="border-collapse: collapse; width: 48pt;" border="0" cellpadding="0" cellspacing="0" width="64"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" align="right" width="64" height="20">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">4


</td> </tr> </tbody></table>How do I do this?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,432
Office Version
365
Platform
Windows
Welcome to the Board!

I don't know if you can use FILL to do this, but you can use a formula to do it:, i.e.

=INT((ROW()-current row number)/3)+1

So if you are starting on row 1, it would look like:
=INT((ROW()-1)/3)+1

If you are starting on row 6, it would look like:
=INT((ROW()-6)/3)+1

Then just copy down for all your rows.
 

thiag.siva

New Member
Joined
Aug 14, 2009
Messages
5
Thanks for the reply.

I tried the formula. It just gives me 'FALSE' as the output.. can you show it with an example?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,432
Office Version
365
Platform
Windows
Can you tell me which row you have entered your formula in, and post the exact formula, as you have it entered in that cell?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
You must have copied it wrong. Joe's formula works quite well!!
lenze
 

thiag.siva

New Member
Joined
Aug 14, 2009
Messages
5
Sorry Joe.. I entered that formula in the wrong cell. It works fine. Thank you very much :)
 

thiag.siva

New Member
Joined
Aug 14, 2009
Messages
5
But what if I want to start from some other number instead of 1.

say for example.. i want it this way

45
45
45
46
46
46
47
47
47
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,432
Office Version
365
Platform
Windows
The last number is the number you want to start counting from:
=INT((ROW()-current row number)/3)+starting number

So, just use:
=INT((ROW()-current row number)/3)+45

If you still need help adapting it to work, I need you to provide the specifics, namely:
1. what row you are starting this fill series in;
2. what number you want to start counting from
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,432
Office Version
365
Platform
Windows
I see you figured it out and posted while I was typing (what can I say, I am a slow typist!). I'll leave the detailed explanation out there for others who might encounter the same question.
 

Forum statistics

Threads
1,081,695
Messages
5,360,679
Members
400,592
Latest member
katekoz

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top