PDA

View Full Version : Solved: Copy This Formula Down Rows



Barryj
08-12-2010, 10:08 AM
Is it possible to copy the following formula down a column, on the page it reads from is a blank row every forth line. Below is the formulas:

=IF('01-01-11'!$F301="",IF('01-01-11'!$F302="",'01-01-11'!$F303,'01-01-11'!$F302),'01-01-11'!$F301)

The next line is:

=IF('01-01-11'!$F305="",IF('01-01-11'!$F306="",'01-01-11'!$F307,'01-01-11'!$F306),'01-01-11'!$F305)

Is there a way to drag the formula down the column and have it proceed four lines ahead.

I guessing this is not possible but worth asking as it will cut a lot of time out.

Thanks.

austenr
08-12-2010, 10:30 AM
You will need to use MOD in the formula but not sure where. xld will probably be along soon.

mdmackillop
08-12-2010, 01:07 PM
Can you post a workbook showing data layout?

gcomyn
08-12-2010, 03:29 PM
you can just select the cells you want to have it in, then Fill>Down (cntrl-D), and the numbers will automatically change. You can either select the entire range, or just those cells that you want it in. If you select the entire range, you will probably have to go back and remove those you don't want...

GComyn

Barryj
08-15-2010, 08:46 AM
Ok, tried the control D but it only increments the formula by 1, not the 4 as required, I have attached a sample of how the data looks and how the formulas are on sheet 1.

I need the formulas to increment 4 for each new line, hope this makes some sense.

mdmackillop
08-15-2010, 09:30 AM
in B3 and copy down
=INDIRECT("'01-01-11'!B" & (ROW()-2)*4+2)

Barryj
08-15-2010, 09:43 AM
Thats not working, if a name is put on top of name already there it shows in red as per sample, and reads that name, that is what the original formula was doing, the suggestion you posted only reads the original name not the alteration.

So I still need the original formula to do that.

gcomyn
08-16-2010, 08:08 AM
here is the combination of what you had originally and what mdmackillop added...


=IF(INDIRECT("'01-01-11'!$B" & (ROW()-2)*4)="",IF(INDIRECT("'01-01-11'!$B" & (ROW()-2)*4+1)="",INDIRECT("'01-01-11'!$B" & (ROW()-2)*4+2),INDIRECT("'01-01-11'!$B" & (ROW()-2)*4+1)),INDIRECT("'01-01-11'!$B" & (ROW()-2)*4))


I've tested in on your example, and it works for me.

GComyn
:sleuth:

Barryj
08-17-2010, 06:45 AM
That works great gcomyn, thank you very much and thanks to all those who responded, will save me a load of time.

I will now mark this as solved, again thank you.