using 'paste link' into seperate tab

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

upsidedown_pw - 09 May 2008 15:06 GMT

I have a parent spreadsheet in tab 1 and on seperate tabs I have chunks of
the information through paste special and paste link. When the parent cell
is empty, the paste link command is putting a zero in instead of leaving it
blank. This is messing up my averages. I have tried changing the category
in format cells but this has no effect.

I anyone knows the answer it'd be much appreciated :)

Reply to this Message

Peo Sjoblom - 09 May 2008 15:21 GMT

You need to edit the link, if it for instance looks like

=Sheet2!$A$1

then change it to

=IF(Sheet2!$A$1="","",Sheet2!$A$1)

Of course if there can be no zeros in your average then you can change the
average formula

=AVERAGE(IF(A2:A100<>0,A2:A100))

or if there can be no zeros nor negative values

=AVERAGE(IF(A2:A100>0,A2:A100))

both formulas entered with ctrl + shift & enter

Signature

Regards,

Peo Sjoblom

--

Regards,

Peo Sjoblom

>I have a parent spreadsheet in tab 1 and on seperate tabs I have chunks of
> the information through paste special and paste link. When the parent
[quoted text clipped - 6 lines]
>
> I anyone knows the answer it'd be much appreciated :)

Reply to this Message

Max - 09 May 2008 15:30 GMT

One option is to use this kind of link formula:
=IF(Sheet2!A1="","",Sheet2!A1)
which will return null strings (text) for any blank cells
(Average will ignore text)

Or, as-is (ie with the zeros),
you could use a conditional average,
eg: =AVERAGE(IF(B1:B7>0,B1:B7))
which has to be array-entered (press CTRL+SHIFT+ENTER)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I have a parent spreadsheet in tab 1 and on seperate tabs I have chunks of
> the information through paste special and paste link. When the parent cell
[quoted text clipped - 3 lines]
>
> I anyone knows the answer it'd be much appreciated :)

Reply to this Message

upsidedown_pw - 09 May 2008 15:43 GMT

Thanks to both of you. A conditional average wont work because I have some
values of zero that I want to include. Its just the data thats not been
input into the parent sheet that I dont want included.

One question though, whats the difference between be using the $ and not?

> One option is to use this kind of link formula:
> =IF(Sheet2!A1="","",Sheet2!A1)
[quoted text clipped - 12 lines]
> >
> > I anyone knows the answer it'd be much appreciated :)

Reply to this Message

Max - 09 May 2008 16:01 GMT

> .. whats the difference between using the $ and not?

$ signs makes the cell ref absolute, ie fixed
Eg In B1: =$A$1 will fix it to A1,
ie the ref won't change when you copy B1 across or down

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Rate this thread: