Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Ontario, Canada
    Thanked 0 Times in 0 Posts

    Data Summary-Reference (Excel 2000)

    I'm maintaining my dvd movie collection on a spreadsheet. Each movie has a separate tab sheet with the dvd info.

    I want to create a master list that only has a summary if title, purchase date, place, & price. The data for 'purchase date' & 'place' are not listed yet on the indivdual movie data sheets. That can be ignored for now.

    On my master list I have 4 columns: title, purchase date, place, & price.

    I'm using an INDIRECT() in the master list for 'title' & 'price' to grab these data from each of the worksheets.

    For the 'title' column, i'm using: =INDIRECT("Casino!"&"A"&ROW()-1)

    For the 'price' column, it is: INDIRECT("'"&A2&"'!"&"B12") (I'm getting a #ref errror on instances where spaces are entered)

    Is there an efficient formula (no macros please) or improvement on the above?

    Please see sample attachment.

    Appreciate any help

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Data Summary-Reference (Excel 2000)

    Indirect is using the sheet name. The name in column A for "Dirty Harry" is "DirtyHarry". You told excel to find get the info from the sheet name called "Dirty Harry" which does not exist, giving an error message.

    On design, personally, I would not have a sheet for each movie. I would create 1 master sheet with each movie in its own row and columns for each of the pieces of data. Then you could use autofilter (eg) to see just the comedies or dramas or whatever.

    You could make a "master display" to look like what you have, you "grabbing" a "record" (=row) from the database and putting each column in its own place.


Posting Permissions

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