Hello,

At the car dealership that I work at, we have an 'master' spreadsheet to track data for our vehicles. Different departments use this spreadsheet for different reasons. For example, the one employee may use it to check on the status of titles, another may use it to give pricing to a potential customer. Because of this, the spreadsheet contains many a lot of cells with data that pertains to many people, with some overlap.

I have been trying to check our pricing to make sure it is in line with our competitors. For that, I would need a handful of information from this 'master' spreadsheet to check the price of these vehicles. I need a separate page, on the same doc, that will do the following:

1. Sort a particular feature of each car model. For example, to sort by year, current price, mileage etc

2. Have the columns in a different order than in the master spreadsheet. I want them in a specific order; different than how the master sheet has it.

3. Update in real time. If a new car is added to the spreadsheet, it should automatically be added onto the other spreadsheet.

4. One page per car model. One page for Tacoma, one page for Tahoe, etc.



I have been told not to touch the master spreadsheet. I can only send the data to another page to manipulate.

Here is what I tried so far. I formatted the master spreadsheet into a table. On a separate page I used '=' then linked each of my desired columns of data into a separate table. I then used the advanced filter button on the top ribbon. This allowed me to filter each car model. I then did this for each page. However, when I would try and sort each column it would give me an error because it is in an array.

The Ideal image shows my work so far, keep in mind that I still cannot sort.
The master shows a small portion of our spreadsheet. It has about 30 columns.

Anybody have any advice for me? Any help would be appreciated!

Attached Images

	Ideal.jpg (103.3 KB, 4 views)
	Master .jpg (62.7 KB, 4 views)

We cannot manipulate data in a picture. Please create a sample file that we can work with that may have 10-15 records. Attach that file so that we can work with actual data and not a picture.

Here you go!

Attached Files

Sample.xlsx (11.5 KB, 2 views)

My advice is, in separate Excel report workbook, create an ODBC query, which reads data from your Master workbook, and displays it in a way you want in this report workbook (columns rearranged, data in query table ordered e.g. by make and by year, etc.

The report table can be set to be refreshed when the report workbook is opened, or by some schedule (what I usually avoid, as the workbook will freeze for some time period [without any control over this from your side], - which depends on the size of source table and on connection speed when query is running), or manually whenever the user wants it (right-clicking on report table and selecting Refresh, or from Data menu selecting Refresh All).
Trying to set another sheet/workbook to be refreshed, when anything is changed in source table, is not a good idea for Excel. Excel events are very limited, and as much as I know, the only one you can use for this, is the one which runs when any cell on sheet is edited (NB! The event always runs even when you double-click on any data cell, and press Enter, without changing anything really!). This means, that work with your Main table may get really sluggish!

The query table in report workbook is really a Defined Table, so there is filtering option set automatically. The easiest way to get only certain model displayed, is to set the filer for this model. Additionally you can reserve some rows at top of page, where you can use SUBTOTAL formulas, to display various statistics (counts, totals, averages, etc.) for filtered rows in certain columns (e.g. min/max prices for selected model).

The another way to get a table of certain model, is to have another sheet (e.g. ModelReport) in report workbook, where at top you have field(s) where you can determine e.g. the model, and there will be an ODBC query similar to first one, but having the reference to condition cell(s) as parameter(s) for WHERE condition, which returns data for determined model whenever the entry in [any] condition cell is changed. I.e. you don't need a sheet for every model - you have a single ModelReport sheet, and can get data for every model there (but for single model only - or for any set of conditions) - simply by selecting a model/conditions you want.

With design like this, having the report workbook opened, doesn't in any way affect the Main workbook. And you can have any number of copies of report workbook, used by different users at same time, and not affecting the work of other users in any way. The only limit is, that those users must have the access (minimally read rights) to Main workbook