copy multiple cells with absolute reference help

[milehigh]

Hi,

I am trying to copy a formula down a sheet so I don't have to type them all separately (over 200).

I am copying data from one sheet to another.

so - cell a2 - formula is =sheet1$b1
cell a3 - formula is =sheet1$b1
cell a4 - formula is =sheet1$c1
cell a5 - formula is =sheet1$c1

And I want to copy all the way down column a
so that cell a6 - formula is =sheet1$d1
cell a7 - formula is = sheet1$d1

and so on.

Thanks in advance for your help.

 

[Dendro]

Here you go, paste the code in cell a2 and copy down. If you are working between sheets you will have to adjust the formula for that

=IF(MOD(ROW();2)=0;INDIRECT(ADDRESS(1;2+(ROW()-2)/2));INDIRECT(ADDRESS(1;2+(ROW()-3)/2)))

 

[milehigh]

Hi Dendro,

Thanks for your help. I can't get this to work. I know that I need to put a column or reference in between the () after ROW - so in this instance I put A2 of the current sheet after the first ROW and the cell from the other Sheet (named SCDF) - SCDF!B1 into the () in the subsequent ROW but I am still getting an error in the formula (mod Number,divisor) So I am not sure what to do.

here is what mine looks like: =IF(MOD(ROW(a2);2)=0;INDIRECT(ADDRESS(1;2+(ROW(SCDF!B1)-2)/2));INDIRECT(ADDRESS(1;2+(ROW(SCDF!B1)-3)/2)))

error is noted after the first semi-colon.

Thanks.

 

[Dendro]

You are not supposed to put anything in ROW(), if it's empty it will give the row-number of the cell where the row()-formula resides. We need this to know if the formula is in an even or uneven row-number.
Let's break down to the formula to understand it better(since i already forgot myself).

=[COLOR=#0000ff]IF([/COLOR][COLOR=#006400]MOD([/COLOR][COLOR=#ff0000]ROW()[/COLOR][COLOR=#006400];2)[/COLOR][COLOR=#000080]=0;...;...)[/COLOR]

1. as i already mentioned above, it returns the row-number the formula is in(i.e if you put this in cell A2 it will return row-number 2)
2. We look if this is an even or uneven number by calculating the modulus of that row number divided by 2
3. IF the modulus is equal to 0 it is an even row-number and we do some stuff(copy certain cells) -see further under condition=TRUE, if the row-number is uneven we want to copy the same cells as the previous cell- see further under condition=FALSE

condition=TRUE

[/COLOR]
[COLOR=#000080]INDIRECT([/COLOR][COLOR=#006400]ADDRESS(1;[/COLOR][COLOR=#ff0000]2+(ROW()-2)/2[/COLOR][COLOR=#006400])[/COLOR][COLOR=#000080])[/COLOR]

let's take the example for cell a2(an even row-number) - formula is =sheet1$b1
1. don't worry about this part yet->we decide which column should be assigned depending on the row the formula is in: for the example it will return 2+(2-2)/2 =2= column B



2. we create an address of row 1 - where all the data is coming from - and a column (see 1)
3. INDIRECT takes this address and makes a cell-reference from it B1

condition=FALSE

INDIRECT(ADDRESS(1;2+(ROW()-3)/2))

what happens for an uneven row-number, for example A3? Let's take a look.
The formula is almost the same but subtracts 1 more from the row number (in this case 3) as to take the same value as the previous cell( in this case A2).
We get 2+(3-3)/2 =2 = column B just as you wanted it to be "cell a3 - formula is =sheet1$b1"

 

[Dendro]

Sorry for double posting, but i wanted to clear that up first. To add the sheet to the cell reference, adjust the INDIRECT formula into

=INDIRECT([COLOR=#ff0000]CONCATENATE("'Sheet1'!";[/COLOR]ADDRESS(...)[COLOR=#ff0000])[/COLOR])