Pasting in successive cells

[Bob]

My subject is probably incorrectly worded but....

What I have is a range of cells in a worksheet that I want to reference
another range of cells in another worksheet in the same workbook. I have
tested this and works fine using =Master!$A$3. This takes the value in the
cell A3 from worksheet Master and places it in the cell A3 on the
destination worksheet. You know how it works.

Now I want to continue this process throughout the range of cells on the
destination sheet. Normally when I want to copy a range of cells it's easy
and it adjusts the formulas for the column the entry is in. Now trying this
where the source cell is on another worksheet is not working the same way.
A real shocker here! I really don't want to manually enter each of these
references to the source worksheet manually. I've tried Paste Special, etc.
but can't get it to work.

Can anyone tell me how I go about doing this? I really appreciate the help.

Bob

 

[RagDyer]

Get rid of the absolute designators ... the dollar signs, and make the
reference relative!

=Master!A3

Then drag down to copy as usual.

If you <Copy>, then <Paste Special>, <Paste Link>,
You'll get the absolute reference by default.

You can enter an "=" sign in A3 of the destination sheet,
navigate to the Master sheet,
click in A3 and hit <Enter>,
And you'll get a link that's relative.

 

[Guest]

HHI,

Try:
=Master!A$3
or
=Master!$A3
or even
=Master!A3

depending on the result you're looking for.

HTH
Jean-Guy

 

[Bob]

Thanks RD.

That worked well except for one issue. When one of the cells on the Master
is a blank it fills the dependant cell with a zero. Being that most of
these are text fields (addresses, names, etc.) this is a problem. Is there
a way to have the field remain blank until such time an entry in made on the
Master sheet which will be requested for the cell?

Thanks again

 

[Guest]

Hi,

=IF(Master!A3="","",Master!A3)

cheers!
Jean-Guy

 

[Guest]

Or Tools/Option/View and uncheck "zero values"

Thanks RD.

That worked well except for one issue. When one of the cells on the Master
is a blank it fills the dependant cell with a zero. Being that most of
these are text fields (addresses, names, etc.) this is a problem. Is there
a way to have the field remain blank until such time an entry in made on the
Master sheet which will be requested for the cell?

Thanks again

 

[Bob]

Excellent! Thank you very much Pinmaster.

Or Tools/Option/View and uncheck "zero values"