Results 1 to 10 of 10
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  2. #2
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

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

    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.....

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    VLOOKUP is the function you want. See Sheet2 in the attached workbook.
    Attached Files Attached Files
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

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

    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.....
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

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

    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 [img]/forums/images/smilies/smile.gif[/img]

    Thank you,
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

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

    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.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

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

    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
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •