Keep getting circular referance

[adamire]

Hi,

I have two excel workbooks - Workbook 1 and workbook 2. There is two columns on each sheet - A and B. One is a driver and one is driven, the breakdown is as follows.

Workbook 1
Column A is Driven by Workbook 2 Column A
Column B is entered here and drives Workbook 2 column B

Workbook 2
Column A is entered here and drives workbook 1 column A
Column B is driven by Workbook 1 column B.

Any thoughts on how to fix this? I have copied and pasted with link but the problem is when i change the sorting of the column A in workbook 2, it also changes on workbook 1 however column B does not change and is therefor out of sync.

I tried to upload an example but it wont let me (i have tried multiple ties in the past with no joy).

Thanks,

Adam

[sandy666]

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

[Sam Capricci]

you may have to upload a sample sheet to have it diagnosed. For example, in A1 workbook1 if I put =workbook2!A1 and a hardcoded value in B1 of workbook1 then a vlookup in workbook2 to pull the result in workbook1 in B1 I don't get a circular reference. But if I make the formula in A1 of workbook1 an index/match from the value in B1 workbook1 using the B1 in workbook2 then I get the circular reference because each is trying to lookup the other.

[adamire]

Hey,

Thanks for the reply, i am not able to upload at all and i have been on to the admin but still is not working i have uploaded it to tinyURL.

http://s000.tinyupload.com/?file_id=...90755403443009

Thanks,

[sandy666]

but there is no any CR because there is no any formula(s)

[adamire]

Hi Sandy,

I know i was only trying to show what im trying to achieve. I had tried that but the only problem is that if you change the order of column A on worksheet 2 it changes on worksheet 1 and the data in B does not match.

hmmm

[sandy666]

so use INDEX/MATCH
you need to be more descriptive with what you want to achieve.

maybe

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[sandy666]

I think I got what you want. But with your criteria you will have CR all the time. Doesn't matter you;ll use simple reference or index/match because reference or formula calling itself so on the end - circular reference (=no result or result incorrect)

Remove or allow a circular reference

maybe try here: Excel Programming / VBA / Macros