Copying data across different worksheets

Hi everyone
I imagine there is a really simple answer for what I am trying to do so I'd be grateful if someone could take the time to point me towards it.
As an excel baby I don't know the language to use to describe what I'm trying to do in 'Help' or when searching through other users questions so apologies if my request duplicates any that have gone before.

So.... what am I trying to do?
In worksheet A (sources) i have a list of foods in column A and and I want all current and future entries to copy to column A in worksheet B (symptoms). The formula I am using for this is fine (='sources'!$a$5) but I don't know how to set it up so that the formula duplicates all the way down the equivalent column in worksheet B. At the moment I am copying the formula from row 5, pasting to row 6 and editing the formula to $6. I'll lose the will to live if I have to do this individually and I'm sure there has to be a better way judging by all the really advanced stuff people are talking about on this forum. An answer that involves simple editing or formating would be much appreciated as the things I've seen in other posts about code is like reading Japanese (which I can't)!

Many thanks
Nic, UK

In your formula the $ tell Excel to copy that exact cell no matter what.
That is why when you try and copy it down it keeps looking for the 1st cell.
Try removing both $ from your formula, then copy that formula down, it should
automatically look for the next cell down rather than looking specifically
for a5.

"Nic M" wrote:

>
> Hi everyone
> I imagine there is a really simple answer for what I am trying to do so
> I'd be grateful if someone could take the time to point me towards it.
> As an excel baby I don't know the language to use to describe what I'm
> trying to do in 'Help' or when searching through other users questions
> so apologies if my request duplicates any that have gone before.
>
> So.... what am I trying to do?
> In worksheet A (sources) i have a list of foods in column A and and I
> want all current and future entries to copy to column A in worksheet B
> (symptoms). The formula I am using for this is fine (='sources'!$a$5)
> but I don't know how to set it up so that the formula duplicates all the
> way down the equivalent column in worksheet B. At the moment I am
> copying the formula from row 5, pasting to row 6 and editing the
> formula to $6. I'll lose the will to live if I have to do this
> individually and I'm sure there has to be a better way judging by all
> the really advanced stuff people are talking about on this forum An
> answer that involves simple editing or formating would be much
> appreciated as the things I've seen in other posts about code is like
> reading Japanese!
>
> Many thanks
> Nic, UK
>
>
> --
> Nic M
> ------------------------------------------------------------------------
> Nic M's Profile: http://www.excelforum.com/member.php...o&userid=34246
> View this thread: http://www.excelforum.com/showthread...hreadid=539973
>
>

Instead of :
(='sources'!$a$5)
wirte in B cellA1) =A!a5

$ makes things absolute referencing. You want relative referencing for you
actions .
Click Copy cell B:A!a5.
Shade all the cells you want write incrementally to on Worksheet B
Paste the cell you have just copied to the shaded cells.
and all the cells in following column cells will be incremented by 1.

If you're mapping an area a1:c6 I preferr to copy a1 column a2,a3,a4,a5
in 1 operation. and a1 b1,c1,d1,e1 in anther copy shade paste operation.
Then just map col a by shading Col a copy, shade remaining cols and paste.

In summary Excel increments 1 cell to all shaded down or across cell when
you paste a relatively mapped cell.

HTH,
John




"Nic M" wrote:

>
> Hi everyone
> I imagine there is a really simple answer for what I am trying to do so
> I'd be grateful if someone could take the time to point me towards it.
> As an excel baby I don't know the language to use to describe what I'm
> trying to do in 'Help' or when searching through other users questions
> so apologies if my request duplicates any that have gone before.
>
> So.... what am I trying to do?
> In worksheet A (sources) i have a list of foods in column A and and I
> want all current and future entries to copy to column A in worksheet B
> (symptoms). The formula I am using for this is fine (='sources'!$a$5)
> but I don't know how to set it up so that the formula duplicates all the
> way down the equivalent column in worksheet B. At the moment I am
> copying the formula from row 5, pasting to row 6 and editing the
> formula to $6. I'll lose the will to live if I have to do this
> individually and I'm sure there has to be a better way judging by all
> the really advanced stuff people are talking about on this forum An
> answer that involves simple editing or formating would be much
> appreciated as the things I've seen in other posts about code is like
> reading Japanese!
>
> Many thanks
> Nic, UK
>
>
> --
> Nic M
> ------------------------------------------------------------------------
> Nic M's Profile: http://www.excelforum.com/member.php...o&userid=34246
> View this thread: http://www.excelforum.com/showthread...hreadid=539973
>
>

There are two easy ways to do this. (1) Hover the mouse over the southeast
corner of the cell with the formula to be copied. When the white cross
changes to a black cross, depress the left mouse button and hold it down,
then drag down the column as far as you need. No problem if you go too far;
you can delete unneeded entries.

(2) Click on the cell with the formula to be copied. Holding down the shift
key, click on the last cell in the column that will contain a formula. All
the intermediate cells will then be selected (darkened). Press Ctrl+D to
fill down.

You can also perform these procedures from left to right. To fill right,
press Ctrl+R.

"Nic M" <Nic.M.27i6an_1147115401.6067@excelforum-nospam.com> wrote in
message news:Nic.M.27i6an_1147115401.6067@excelforum-nospam.com...
>
> Hi everyone
> I imagine there is a really simple answer for what I am trying to do so
> I'd be grateful if someone could take the time to point me towards it.
> As an excel baby I don't know the language to use to describe what I'm
> trying to do in 'Help' or when searching through other users questions
> so apologies if my request duplicates any that have gone before.
>
> So.... what am I trying to do?
> In worksheet A (sources) i have a list of foods in column A and and I
> want all current and future entries to copy to column A in worksheet B
> (symptoms). The formula I am using for this is fine (='sources'!$a$5)
> but I don't know how to set it up so that the formula duplicates all the
> way down the equivalent column in worksheet B. At the moment I am
> copying the formula from row 5, pasting to row 6 and editing the
> formula to $6. I'll lose the will to live if I have to do this
> individually and I'm sure there has to be a better way judging by all
> the really advanced stuff people are talking about on this forum An
> answer that involves simple editing or formating would be much
> appreciated as the things I've seen in other posts about code is like
> reading Japanese!
>
> Many thanks
> Nic, UK
>
>
> --
> Nic M
> ------------------------------------------------------------------------
> Nic M's Profile:
> http://www.excelforum.com/member.php...o&userid=34246
> View this thread: http://www.excelforum.com/showthread...hreadid=539973
>

Works a treat and so simply explained.
Much appreciated
Nic