In Excel how can you have 2 identical columns in 2 worksheets?

[Buzzard7]

I am working on an Excel spreadsheet.
The spreadsheet has external data that refreshes every time it is open.
I have three worksheets in the spreadsheet.
I want a column to be the same on one sheet as it is the other.
I also have auto filtering on, so I want the column to change on one sheet when it changes on the other.

 

[SkipVought]

Buzzard7,

You have an Excel Workbook with 3 Worksheets.

One worksheet is refreshed with an external data query.

So you want worksheet 2 to reflect the data on worksheet 1? But you can't predict the number of rows that worksheet 1 will have as a result of the query.

What is the purpose of worksheet 2? What will worksheet 2 accomplish that worksheet 1 alone will not?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Buzzard7]

2 worksheet have external data. The 3rd worksheet is to compare differences.

 

[SkipVought]

What technique do you want to use to compare...

something as simple as
=if(sheet1!A2=sheet2!A2,"Match","No Match&quot

of what?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Buzzard7]

Sheet1 A1 = 2
Sheet2 A1 = 6
Sheet3 A1 = Sheet1 A1
Sheet3 A2 = Sheet2 A1
Sheet3 A3 = A1 - A2

 

[SkipVought]

So, what's the question?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Buzzard7]

How can you make a column identical to another column in a different worksheet, with the data able to change in one column and the other column change to be identical with the new data?

 

[SkipVought]

In sheet 1 A1

=Sheet2!A1

What am I missing?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Buzzard7]

That doesn't work with auto filtering.
I need the whole column. ex.(A:A)

=Sheet2!A2 doesn't work because say the auto filter has A2 filtered out.

 

[SkipVought]

Want to do some VBA coding? Don't have time now.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Dinobrago]

Just to clarify, you are looking for the following behavior:

If anything in Sheet1!A:A changes, you want that change to be reflected in Sheet2!A:A?

Are these changes occurring at load time only? That is, when the worksheet is refreshed with an external data query on opening, you want the changes in Sheet1!A:A to be reflected in Sheet2.

Is that correct?

If so, you can set up a macro to run on load. Otherwise, if there are modifications are random times, you'll need to take a different approach.

 

[SkipVought]

Its' not just a change on the target sheet. It is detecting the hidden rows

So if Sheet2 has row 2 hidden by virtue of a filter, what do you want to see on Sheet1? The values in Sheet2 Row 3 in Sheet1 row 2 and so on?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Dinobrago]

Again, just to be clear.

Are there changes to the filters that occur at random times?

That is, I change a filter on Sheet1 and I want the changes to be propagated to Sheet2. That is, if a row is hidden on Sheet1, I want that same row to be hidden on Sheet2?

Is the relation between Sheet1 & Sheet2 such that the same filter can be applied to Sheet2

OR...

The filter on Sheet1 hides certain rows and you want to hide those same rows on Sheet2 but can't do it through a filter?

-d

 

[SkipVought]

See that's the problem. A filter change is NOT a trappable event.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Dinobrago]

One step at a time!

So, is the answer to my question that you are making changes to filters dynamically at random times and you want the hidden rows to be propagated through the different worksheets?

d-