# Simple Copy and Paste Question

#### Jamesrwilliams

##### New Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### erik.van.geit

##### MrExcel MVP
Hi, James,
try something like this
Code:
``=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 ?
Code:
``=INDIRECT("discussion!\$A"&ROUND(ROW()/10,0)+4)``

#### Jamesrwilliams

##### New Member
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

##### New Member
Not to worry, sorted it now.

Thank you for all your help

James

Replies
2
Views
287
Replies
2
Views
250
Replies
3
Views
92
Replies
2
Views
141
Replies
6
Views
205

1,195,937
Messages
6,012,410
Members
441,698
Latest member
DaveTeo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back