A
Andrew
This is the general task. The data on each sheet
consists of an Artist, Song and Sales Figures for that
song. The columns are in that order.
Sample:
The Beatles.....Let It Be....$56,000......52
Beethoven.......Fidelio.....$100,000.......8
The Beatles.....Let It Be.....$4,988......99
The Beatles.....Hey Jude.....$35,267.......3
There is one sheet per year. ('Year 2000', 'Year 2001',
etc.)
I have an aggregate sheet at the end of the workbook,
summing all the figures by SONG TITLE.
The method I used was to consolidate the sheets by using
Data > Consolidate. Of course the row label is the Song
Title in column B.
Now I simply need to bring the artist's names to the
aggregate sheet. So, I insert a column on the aggregate
sheet and use a combination of INDEX and MATCH.
My problem (finally) is how to reference the range of
artist's names on all the sheets since not all songs
appear on all sheets.
It's probably a simple syntax solution. But right now I
only know how to reference multiple sheets if the ranges
are the same exact cells.
I've tried named ranges, but the names must be unique
within a workbook as you know.
Or maybe there is an entirely different way to approach
this.
Thank you all!
consists of an Artist, Song and Sales Figures for that
song. The columns are in that order.
Sample:
The Beatles.....Let It Be....$56,000......52
Beethoven.......Fidelio.....$100,000.......8
The Beatles.....Let It Be.....$4,988......99
The Beatles.....Hey Jude.....$35,267.......3
There is one sheet per year. ('Year 2000', 'Year 2001',
etc.)
I have an aggregate sheet at the end of the workbook,
summing all the figures by SONG TITLE.
The method I used was to consolidate the sheets by using
Data > Consolidate. Of course the row label is the Song
Title in column B.
Now I simply need to bring the artist's names to the
aggregate sheet. So, I insert a column on the aggregate
sheet and use a combination of INDEX and MATCH.
My problem (finally) is how to reference the range of
artist's names on all the sheets since not all songs
appear on all sheets.
It's probably a simple syntax solution. But right now I
only know how to reference multiple sheets if the ranges
are the same exact cells.
I've tried named ranges, but the names must be unique
within a workbook as you know.
Or maybe there is an entirely different way to approach
this.
Thank you all!