Copying problem

[MartinW]

Hi Group,



I have 2 sheets. One called WORKSHEET one called REPORT.

In the report sheet I want to copy a formula down the page so

that the referenced cells increment by 12 instead of 1 as per

below.



C18 =IF(WORKSHEET!C10=0,"",WORKSHEET!C12)

C19 =IF(WORKSHEET!C22=0,"",WORKSHEET!C24)

C20 =IF(WORKSHEET!C34=0,"",WORKSHEET!C36)



I feel like this should be very simple but I just can't work it out,

Any help would be much appreciated.

 

[Biff]

Hi!

Try this:

=IF(OFFSET(WorkSheet!C$10,(ROWS($1:1)-1)*12,,)="","",OFFSET(WorkSheet!C$12,(ROWS($1:1)-1)*12,,))

Biff

 

[Biff]

Oops!

You were testing for 0 and I wrote the formula testing for blank!

Just change ="" to =0.

An empty cell evaluates to 0 so the formula will still return blank.

Biff

 

[Ken Johnson]

Hi Martin,

Or this:

=IF(INDIRECT("WORKSHEET!C" & (ROW()-18)*12 + 10)
=0,"",INDIRECT("WORKSHEET!C" & (ROW()-18)*12 +12))

Ken Johnson

 

[MartinW]

Thanks Biff, Works Great!!!

 

[MartinW]

Hi Ken,

I couldn't get this one to work. At first I think I confused the issue by
trying to copy and paste to an empty cell direct from your post but then
when I inputted the formula manually it only came up with #VALUE. I'm
betting that the problem is at my incompetent end but seeing that Biffs
reply works I'll go with that for now. When I've got more time I'll go
through both of them and see what I can learn.
Thanks for taking the time to post.

Cheers
Martin

 

[Ken Johnson]

Hi Martin,
Biff's formula not only works, it's also shorter!
Thanks for the feedback anyhow.
Ken Johnson

 

[Biff]

You're welcome. Thanks for the feedback!

Biff