Results 1 to 2 of 2
2004-01-23, 16:05 #1
- 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
2004-01-23, 16:37 #2
- 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.