Do you use a multicell array for this?? (Excel 97)

Topic

New Reply

Easiest way is to use Autofilter (Data – Filter – Autofilter) then select the DMA and your data will collapse to only display the data of interest. You can copy this to where you want.

You could also use the Advanced filter to copy it elsewhere but I like the hiding etc you get so you can edit delete etc in your actual data. It also seems more intuitive to many users.

Steve

Reply | Quote

Replies

Thanks for your reply, but that won’t work for what I’m doing…

I actually have about 8 worksheets in the workbook and the summary sheet will be collecting data from each tab based on what DMA is typed in…..

Reply | Quote

Why can’t the data be all put together into 1 spreadsheet. You could add another column (division, department, manager or whatever) as the distinction you are now doing by sheets. You get that particular “sheet”, again using the Autofilter.

Otherwise it seems like you will have to do some VB coding to get what you want.
Steve

Reply | Quote

Hi Steve,

I have reposted my attachment because I attached the wrong file. ‘

As I mentioned in my other post the actual spreadsheet is much, much larger. The sheet that most of the data will come from goes out some 30 odd columns. Most of this data is not needed by a particular group of people who need to look at the data in a clean summary format (They also need to print out the summary)

Auto-filter will not work for what I am doing.

Reply | Quote

Hi,

Take a look at the attached and see if it dows what you want. The formulae have been set up to use the first 100 rows on sheet1, but you can change that with Search/Replace.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I know this was kind of closed, but I have been thinking about this and finally had a chance to play with it.
Instead of typing in the values, I added a combobox for selecting.
The combobox is filled/refilled with sorted unique values from the DMA list whenever sheet1 is changed, so as you add more data, it will keep updating itself.

Steve

Reply | Quote

Hi Everyone,

I have attached a sample worksheet. Here is what I would like to do. I would like to type in a DMA number in Sheet 2 Cell B1. In the appropriate cells below this I would like all of the data that is in the DMA (from Sheet 1) to show up in the table.

Not sure what I have to do to make this happen? I think vlookups, offset, arrays???

Could someone help me

Thank you,

Reply | Quote

VLOOKUP is the function you want. See Sheet2 in the attached workbook.

Reply | Quote

Oops..

I attached the wrong file!!!!

I have attached the correct file that I wanted and changed my original post with the right file.

The reason a VLOOKUP won’t work in what I am attempting to do is that there is more than 1 unit in each DMA. As you can see in my new attachment I want to see what units fall in that DMA…..

Reply | Quote

The attached workbook contains a VBA routine in the Worksheet Change Event routine for worksheet Sheet2 that I think does what you want.

Reply | Quote