Simple Copy and Paste Question

[Jamesrwilliams]

Hi all,

I have the following formula in A5

=IF(Discussion!$A5<>"",Discussion!A5,0)

Now, problem comes when I copy this down to the next row on my sheet which is A15 the formula changes to (as it shoud)

=IF(Discussion!$A15<>"",Discussion!A15,0)

But it actually needs to be refering to 'Discussion!$A6' not A15 and so on.

Any suggestions on how I can rectify this? I'm sure there's an easy way and I'm just missing it.

Many thanks in advance


James

 

[erik.van.geit]

Hi, James,
try something like this

=IF(INDIRECT("discussion!$A"&ROUND(ROW()/10,0)+4)<>"",INDIRECT("discussion!$A"&ROUND(ROW()/10,0)+4),0)

kind regards,
Erik

EDIT: isn't this enough ?

=INDIRECT("discussion!$A"&ROUND(ROW()/10,0)+4)

 

[Jamesrwilliams]

Thanks.

In A5 it is returning a value of 1, when copied to the next available cell (10 rows down) it is returning with a value of a list from 1 to 7

Any suggestions?

As i'm sure your aware the current formula is just returning what is inserted in sheet 'Discussion' A5, and returning 0 if nothing inserted. I have around 6 cells all with the same formula. I originally had this formula in A5 and then vlookups in the other 5 returning various information from the Discussions sheet.

Thanks for your help.

 

[Jamesrwilliams]

Not to worry, sorted it now.

Thank you for all your help

James