Pasting between workbooks

aferree · Apr 30th, 2002, 02:42 PM

Is it possible to update a cell in worksheet A based on a value in worksheet B? Maybe I can explain it better. I have a worksheet ("Worksheet 1") with columns A thru D for example that I send to end users. They add a columm E and insert comments to explain certain rows of data. Two days later I'll refresh the information and send them a new worksheet ("Worksheet 2") with columns A - D.
Is it possible to update column E on "Worksheet 2" with values from column E on "Worksheet 1" where column A in "Worksheet 2" matches column A in "Worksheet 1"? I hope this makes sense.

Brian from Maui · Apr 30th, 2002, 05:25 PM

Not sure if this is what you want but...

In worksheet 2 in a cell enter:

=worksheet1!E1 and copy and paste down however many rows

Yogi Anand · Apr 30th, 2002, 07:30 PM

Hi Brian and aferree:

I believe when aferree is talking about Worksheet1 and Worksheet2, these are not worksheets from the same workbook, from what I can understand from aferree's statements, these are from two different workbooks. So, if that is the case reference to the workbook may have to be added to the linking formulas.
aferree, please do post if the worksheets are from two different workbooks.

Regards!

Yogi Anand

Brian from Maui · Apr 30th, 2002, 08:09 PM

Aloha aferre,

If it's two separate workbooks enter in a cell:

='[workbookname.xls]worksheet'1!$E$1

Yogi..is this correct?

aferree · Apr 30th, 2002, 09:24 PM

Yes, it's two different workbooks. What I'm trying to replicate is an Update query in Access (if that makes any sense)... Set E2 = E1 where A1=A2. E2 in this case would represent E column in "workbook B" and E1 would represent E column in "workbook A".I don't think I can set a reference to the cell the worksheet from "workbook A" b/c the worksheet in "workbook B" could have more or less rows.

Yogi Anand · Apr 30th, 2002, 09:55 PM

Quote:

On 2002-04-30 19:09, Brian from Maui wrote:
Aloha aferre,

If it's two separate workbooks enter in a cell:

='[workbookname.xls]worksheet'1!$E$1

Yogi..is this correct?

Hi Brian:
It will be
=[workbookname.xls]worksheet1!$E$1

Regards!

Yogi Anand

Chris Davison · May 1st, 2002, 01:46 PM

Yogi !

I don't like those $ signs

Also, I think the poster requires those links *if* a match on the respective columns "A" occurs, so we may need to present it something along the lines of :

=IF(A1='[Worksheet 1.xls]Sheet1'!A1,'[Worksheet 1.xls]Sheet1'!E1,"something else")

entered into worksheet 2, cell E1

with no anchoring dollar signs so we can copy down

whaddya reckon ?

Yogi Anand · May 1st, 2002, 03:31 PM

Hi Chris:
I believe you are right -- you have picked up all the pieces -- my post was limited to pointing out to Brian that when the OP mentioned Worksheet1, and Worksheet2, these were not sheets of a workbook, but were indeed two different workbooks -- so I commented on the syntax of the linking formulation. Thanks for a close look at the whole issue.

Regards!

Yogi Anand

Chris Davison · May 1st, 2002, 04:13 PM

ahhh yes, I see that now...

Can you help with the last sentence of the posters last message ? it's confusing me a bit....

(usually when something doesn't quite make sense, it means the whole orientation of the question is totally different from what you were interpretting it as !)

aferree · May 2nd, 2002, 08:00 AM

Hey Chris,
That worked perfectly. Thanks. One follow-up question. Right now the formula looks for matches from column A1 in active worksheet to column A1 in the worksheet 1 and inserts the information from column E1. Is there anyway to have it search the entire column for a match and insert the respective information from the E cell in the same row.

Just say for example the match for column A1 of active worksheet was actually in A4 of worksheet 1.

This is what I meant by the active worksheet could have more or less rows than worksheet 1.

Thanks in advance.

[ This Message was edited by: aferree on 2002-05-02 13:38 ]

[ This Message was edited by: aferree on 2002-05-02 13:40 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 30th, 2002, 02:42 PM	#1
aferree New Member Join Date: Apr 2002 Location: Charlotte, NC Posts: 3	Is it possible to update a cell in worksheet A based on a value in worksheet B? Maybe I can explain it better. I have a worksheet ("Worksheet 1") with columns A thru D for example that I send to end users. They add a columm E and insert comments to explain certain rows of data. Two days later I'll refresh the information and send them a new worksheet ("Worksheet 2") with columns A - D. Is it possible to update column E on "Worksheet 2" with values from column E on "Worksheet 1" where column A in "Worksheet 2" matches column A in "Worksheet 1"? I hope this makes sense.

Apr 30th, 2002, 05:25 PM	#2
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Not sure if this is what you want but... In worksheet 2 in a cell enter: =worksheet1!E1 and copy and paste down however many rows

Apr 30th, 2002, 07:30 PM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Brian and aferree: I believe when aferree is talking about Worksheet1 and Worksheet2, these are not worksheets from the same workbook, from what I can understand from aferree's statements, these are from two different workbooks. So, if that is the case reference to the workbook may have to be added to the linking formulas. aferree, please do post if the worksheets are from two different workbooks. Regards! Yogi Anand

Apr 30th, 2002, 08:09 PM	#4
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Aloha aferre, If it's two separate workbooks enter in a cell: ='[workbookname.xls]worksheet'1!$E$1 Yogi..is this correct?

Apr 30th, 2002, 09:24 PM	#5
aferree New Member Join Date: Apr 2002 Location: Charlotte, NC Posts: 3	Yes, it's two different workbooks. What I'm trying to replicate is an Update query in Access (if that makes any sense)... Set E2 = E1 where A1=A2. E2 in this case would represent E column in "workbook B" and E1 would represent E column in "workbook A".I don't think I can set a reference to the cell the worksheet from "workbook A" b/c the worksheet in "workbook B" could have more or less rows.

May 1st, 2002, 01:46 PM	#7
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Yogi ! I don't like those $ signs Also, I think the poster requires those links if a match on the respective columns "A" occurs, so we may need to present it something along the lines of : =IF(A1='[Worksheet 1.xls]Sheet1'!A1,'[Worksheet 1.xls]Sheet1'!E1,"something else") entered into worksheet 2, cell E1 with no anchoring dollar signs so we can copy down whaddya reckon ?

May 1st, 2002, 03:31 PM	#8
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Chris: I believe you are right -- you have picked up all the pieces -- my post was limited to pointing out to Brian that when the OP mentioned Worksheet1, and Worksheet2, these were not sheets of a workbook, but were indeed two different workbooks -- so I commented on the syntax of the linking formulation. Thanks for a close look at the whole issue. Regards! Yogi Anand

May 1st, 2002, 04:13 PM	#9
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	ahhh yes, I see that now... Can you help with the last sentence of the posters last message ? it's confusing me a bit.... (usually when something doesn't quite make sense, it means the whole orientation of the question is totally different from what you were interpretting it as !)

May 2nd, 2002, 08:00 AM	#10
aferree New Member Join Date: Apr 2002 Location: Charlotte, NC Posts: 3	Hey Chris, That worked perfectly. Thanks. One follow-up question. Right now the formula looks for matches from column A1 in active worksheet to column A1 in the worksheet 1 and inserts the information from column E1. Is there anyway to have it search the entire column for a match and insert the respective information from the E cell in the same row. Just say for example the match for column A1 of active worksheet was actually in A4 of worksheet 1. This is what I meant by the active worksheet could have more or less rows than worksheet 1. Thanks in advance. [ This Message was edited by: aferree on 2002-05-02 13:38 ] [ This Message was edited by: aferree on 2002-05-02 13:40 ]