Sorting with two linked tabs

[fjest004]

Hi there,

I am having issues sorting columns on a given worksheet. When a column is sorted in one tab, it is jumbling another column on a linked tab. Vlookup formulas are being used throughout the worksheet.

So, for example in the attached file;

Currently the way the file is set up is when a new project comes in, we put initials in the 'Audit Date' column of the 'To Be Audited' tab. That then pulls over to the 'Customer' tab via the 'No Edits-Audit Sheet'.

I want to be able to sort in the 'Customer' Tab the states, city or state A to Z and not have it mess up the 'To Be Audited' tab. Right now, when someone sorts one of the columns, the initials in the 'to be audited' tab jumble, and the initials are no longer with the original project.

Please can someone give me an idea as to why this is happening.

Thanks a lotExample.xlsm

[p24leclerc]

Your whole table should be sorted. You just have to select which column will be used for sorting. Not just one column of the table because then you loose other columns references.

[fjest004]

Thank you for responding, much appreciated.

I guess I'm not really following what you're saying though. When I sort something in the first tab, that whole table sorts fine, but the last tab gets messed up. Could you please elaborate on what you mean by sorting the whole table.

Thanks again

[p24leclerc]

It is quite simple. The Site # column in your last tab is dependant of the result of a Vlookup when it should be a fix value as the initials you enter in the Date column.
In Customer tab, the Initials column is a Vlookup of the last tab.
It is somewhat like a circular formula.
As I said, your Site # in the tabs other than Customers should be fix values so that your Vlookup will work even if you sort your Customer tab.

[p24leclerc]

It is quite simple. The Site # column in your last tab is dependant of the result of a Vlookup when it should be a fix value as the initials you enter in the Date column.
In Customer tab, the Initials column is a Vlookup of the last tab.
It is somewhat like a circular formula.
As I said, your Site # in the tabs other than Customers should be fix values so that your Vlookup will work even if you sort your Customer tab.

[fjest004]

The only problem with that is I only want to pull over sites that are marked 'To Be Audited' in the 'Audit Status' column of the Customer tab. Also, my site # column is dependent of an if statement, not sure if that makes a difference.

[p24leclerc]

Why do you want to use 2 extra tabs to do something you can do in Customer tab directly?
You only have to filter your Customer tab to show only rows with column I = "To Be Scheduled".
You can directly put the initials in there.

[fjest004]

Sorry, I could have filled you in on that. The file that I use actually has around 10 customer tabs, so we want to have a list of all in one grouping.

[p24leclerc]

Then, you'd probably need a macro to extract them in the last tab. What are the names of all your tabs. Can you supply a file wiht all tabs in it (maybe without data)?

[p24leclerc]

Here is a macro that will extract all To Be Scheduled audit in every Customer sheet you have.

Please Login or Register  to view this content.

Here I assume that all customer sheets have the letters "Cust" in its name and that no other sheet's name contain those letters.
You'll probably have to change the Vlookup formula in your Customer sheets to look at "To be Audited" sheet instead of "NO EDITS-Audit Sheet"

[fjest004]

Very sorry for the long delay. I tried your example and it seems to work very well. I am supplying a example with some names in it. I am assuming that all of the data that is pulling over is going to have to be in the same columns with this macro? or is that not the case? Master Example.xlsm

[p24leclerc]

The way it is programmed, all data will be copied in the same columns.
If you want to know from which sheet the data comes from, the macro can be modified to add a column with the sheet name in it.
Or the macro can be changed to copy data from every sheet in a different place.

Also, in the attached file, I see that your sheets name are Retail, Commercial and Residential. Are those the sheets you want the macro to look at? If so, the macro will have to be modified. Will there be more sheets than those 4 in your file?

[fjest004]

There will be 11 sheets that the macro will have to extract information from. It sounds like it would be easier if the data had a sheet name to be referenced to me, but if it is easier to program with out, no big deal. I would like all of the data to go to one sheet.

[p24leclerc]

Will all the sheets have the letters CUST in there name?

[p24leclerc]

Here the modified macro to get the sheet name in the last column of the table. You can filter the sheet names in order to see data from one sheet at a time.

Please Login or Register  to view this content.

[fjest004]

All sheets will have different names without the words "cust" in them. Can I just add all of the those names where I see "Cust" in the macro with commas?

[p24leclerc]

If you can't or do not want to modify the sheets' names, you can specifically mention sheets names in the IF statement like this:

Please Login or Register  to view this content.

You can add more OR lines so you cover all of your customer sheets.

[fjest004]

The program seems to work great. There is only one issue that I have, and was wondering if there is a way to work this out. In the To Be Audited tab, we need to be able to put initials and a date in the audit date column. Right now when you run the program it deletes all information in that column. I would say we can just put this information in the individual tabs, but we need to be able to just put these initials in the to be audited tab.

[p24leclerc]

This modification of the macro will not erase previous data and it will prevent duplicates.

Please Login or Register  to view this content.