Hi,
I have a master worksheet which consists of
Column A1:A5000 is numbers 1-5,000 (being ID numbers on tickets)
Column B1:B5000 is text (being one of six place names, venues for ticket
sales)

In six other (child) worksheets, named for the venues, I'd like to create
lists that others can use to record whether a specific ticket is sold. For
example, if "Townsville" gets tickets 250 to 1200, this would be recorded on
the Parent list and the Townsville worksheet would only list those numbers -
with no blank rows! I may need to amend the master list from time to time-
e.g. if tickets aren't sold in Townsville, they can be re-assigned to
Cityville (and so disappear from the former worksheet and show up in the
latter).

I think I need MATCH, INDEX and\or ROW to do this, but after trying sevreal
options, I'm no closer.

Any ideas? Thanks in anticipation.

Try the below formula. Please note that this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

Sheet1 named as 'Master'
Subsequent sheets named as 'Townsville',and other 5 venues..

In inidividual sheet in Cell A1 enter the below formula and copy that down
as required....

(all in one line)
=IF(ROW()COUNTIF(Master!$B$1:$B$5000,MID(CELL("fi lename",D1),FIND("]",CELL("filename",D1))+1,99)),"",INDEX(Master!$A$1 :$A$5000,SMALL(IF(Master!$B$1:$B$5000=MID(CELL("fi lename",D1),FIND("]",CELL("filename",D1))+1,99),Master!$A$1:$A$5000), ROW())))

Try and feedback


If this post helps click Yes
---------------
Jacob Skaria


"Vibeke" wrote:

Hi,
I have a master worksheet which consists of
Column A1:A5000 is numbers 1-5,000 (being ID numbers on tickets)
Column B1:B5000 is text (being one of six place names, venues for ticket
sales)

In six other (child) worksheets, named for the venues, I'd like to create
lists that others can use to record whether a specific ticket is sold. For
example, if "Townsville" gets tickets 250 to 1200, this would be recorded on
the Parent list and the Townsville worksheet would only list those numbers -
with no blank rows! I may need to amend the master list from time to time-
e.g. if tickets aren't sold in Townsville, they can be re-assigned to
Cityville (and so disappear from the former worksheet and show up in the
latter).

I think I need MATCH, INDEX and\or ROW to do this, but after trying sevreal
options, I'm no closer.

Any ideas? Thanks in anticipation.

Hi "Vibeke"

Missed to mention few points.

1. The workbook should be saved before trying out the formula
2. The sheetname should be exactly same as the venue; meaning; there should
not be any blank spaces etc; in sheet tab names..
3. As the formula picks the sheetname and compare with the 'Master' sheet;
the same formula applies to all 6 venue sheets

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try the below formula. Please note that this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

Sheet1 named as 'Master'
Subsequent sheets named as 'Townsville',and other 5 venues..

In inidividual sheet in Cell A1 enter the below formula and copy that down
as required....

(all in one line)
=IF(ROW()COUNTIF(Master!$B$1:$B$5000,MID(CELL("fi lename",D1),FIND("]",CELL("filename",D1))+1,99)),"",INDEX(Master!$A$1 :$A$5000,SMALL(IF(Master!$B$1:$B$5000=MID(CELL("fi lename",D1),FIND("]",CELL("filename",D1))+1,99),Master!$A$1:$A$5000), ROW())))

Try and feedback


If this post helps click Yes
---------------
Jacob Skaria


"Vibeke" wrote:

Hi,
I have a master worksheet which consists of
Column A1:A5000 is numbers 1-5,000 (being ID numbers on tickets)
Column B1:B5000 is text (being one of six place names, venues for ticket
sales)

In six other (child) worksheets, named for the venues, I'd like to create
lists that others can use to record whether a specific ticket is sold. For
example, if "Townsville" gets tickets 250 to 1200, this would be recorded on
the Parent list and the Townsville worksheet would only list those numbers -
with no blank rows! I may need to amend the master list from time to time-
e.g. if tickets aren't sold in Townsville, they can be re-assigned to
Cityville (and so disappear from the former worksheet and show up in the
latter).

I think I need MATCH, INDEX and\or ROW to do this, but after trying sevreal
options, I'm no closer.

Any ideas? Thanks in anticipation.

Hi,

The real complication here comes when you say the users are going to record
whether a specific ticket sold. The fact is that depending on how you add
new records to the source, or take records away, this may not be possible
without resorting to VBA.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Vibeke" wrote:

Hi,
I have a master worksheet which consists of
Column A1:A5000 is numbers 1-5,000 (being ID numbers on tickets)
Column B1:B5000 is text (being one of six place names, venues for ticket
sales)

In six other (child) worksheets, named for the venues, I'd like to create
lists that others can use to record whether a specific ticket is sold. For
example, if "Townsville" gets tickets 250 to 1200, this would be recorded on
the Parent list and the Townsville worksheet would only list those numbers -
with no blank rows! I may need to amend the master list from time to time-
e.g. if tickets aren't sold in Townsville, they can be re-assigned to
Cityville (and so disappear from the former worksheet and show up in the
latter).

I think I need MATCH, INDEX and\or ROW to do this, but after trying sevreal
options, I'm no closer.

Any ideas? Thanks in anticipation.

Hi Jacob,
Thank you. Unfortunately I'm not getting any results from that, but I'm
probably doing something wrong. Can you please confirm the formula is
entered in the child worksheets? I'm not familiar with the CELL formula, and
so am unsure about the use of "filename" and D1 (that is, do I simply use
that text as you've written, "filename", or do I insert the name of my file
here? And D1, at this stage, has no data in any of the worksheets, so I'm
unsure how it contributes to the fomula). Could you please elaborate?

Many thanks.

Vibeke

"Jacob Skaria" wrote:

Try the below formula. Please note that this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

Sheet1 named as 'Master'
Subsequent sheets named as 'Townsville',and other 5 venues..

In inidividual sheet in Cell A1 enter the below formula and copy that down
as required....

(all in one line)
=IF(ROW()COUNTIF(Master!$B$1:$B$5000,MID(CELL("fi lename",D1),FIND("]",CELL("filename",D1))+1,99)),"",INDEX(Master!$A$1 :$A$5000,SMALL(IF(Master!$B$1:$B$5000=MID(CELL("fi lename",D1),FIND("]",CELL("filename",D1))+1,99),Master!$A$1:$A$5000), ROW())))

Try and feedback


If this post helps click Yes
---------------
Jacob Skaria


"Vibeke" wrote:

Hi,
I have a master worksheet which consists of
Column A1:A5000 is numbers 1-5,000 (being ID numbers on tickets)
Column B1:B5000 is text (being one of six place names, venues for ticket
sales)

In six other (child) worksheets, named for the venues, I'd like to create
lists that others can use to record whether a specific ticket is sold. For
example, if "Townsville" gets tickets 250 to 1200, this would be recorded on
the Parent list and the Townsville worksheet would only list those numbers -
with no blank rows! I may need to amend the master list from time to time-
e.g. if tickets aren't sold in Townsville, they can be re-assigned to
Cityville (and so disappear from the former worksheet and show up in the
latter).

I think I need MATCH, INDEX and\or ROW to do this, but after trying sevreal
options, I'm no closer.

Any ideas? Thanks in anticipation.

You need to use the same formula without any changes in cell A1 (array entered)

I have tested this as follows
1. In a new workbook rename sheet1 to 'Master' and Sheet2 to 'Townsville'
2. Save the workbook.
3. In master enter the numbers 1 to 10 in Column A and corresponding venus
in B1:B10. Place Townsville in aganist number 5 and 10.
4. In Sheet 'Townsville' cell A1 enter the formula (Array entered) and copy
that down (say to 10 rows)
5. The first two rows A1 and A2 should display 5 and 10


If this post helps click Yes
---------------
Jacob Skaria


"Vibeke" wrote:

Hi Jacob,
Thank you. Unfortunately I'm not getting any results from that, but I'm
probably doing something wrong. Can you please confirm the formula is
entered in the child worksheets? I'm not familiar with the CELL formula, and
so am unsure about the use of "filename" and D1 (that is, do I simply use
that text as you've written, "filename", or do I insert the name of my file
here? And D1, at this stage, has no data in any of the worksheets, so I'm
unsure how it contributes to the fomula). Could you please elaborate?

Many thanks.

Vibeke

"Jacob Skaria" wrote:

Try the below formula. Please note that this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

Sheet1 named as 'Master'
Subsequent sheets named as 'Townsville',and other 5 venues..

In inidividual sheet in Cell A1 enter the below formula and copy that down
as required....

(all in one line)
=IF(ROW()COUNTIF(Master!$B$1:$B$5000,MID(CELL("fi lename",D1),FIND("]",CELL("filename",D1))+1,99)),"",INDEX(Master!$A$1 :$A$5000,SMALL(IF(Master!$B$1:$B$5000=MID(CELL("fi lename",D1),FIND("]",CELL("filename",D1))+1,99),Master!$A$1:$A$5000), ROW())))

Try and feedback


If this post helps click Yes
---------------
Jacob Skaria


"Vibeke" wrote:

Hi,
I have a master worksheet which consists of
Column A1:A5000 is numbers 1-5,000 (being ID numbers on tickets)
Column B1:B5000 is text (being one of six place names, venues for ticket
sales)

In six other (child) worksheets, named for the venues, I'd like to create
lists that others can use to record whether a specific ticket is sold. For
example, if "Townsville" gets tickets 250 to 1200, this would be recorded on
the Parent list and the Townsville worksheet would only list those numbers -
with no blank rows! I may need to amend the master list from time to time-
e.g. if tickets aren't sold in Townsville, they can be re-assigned to
Cityville (and so disappear from the former worksheet and show up in the
latter).

I think I need MATCH, INDEX and\or ROW to do this, but after trying sevreal
options, I'm no closer.

Any ideas? Thanks in anticipation.

Hi,

You may refer to question 7 of the following link -
http://ashishmathur.com/knowledgebaseII.aspx. This should get you what you
want.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vibeke" wrote in message
...
Hi,
I have a master worksheet which consists of
Column A1:A5000 is numbers 1-5,000 (being ID numbers on tickets)
Column B1:B5000 is text (being one of six place names, venues for ticket
sales)

In six other (child) worksheets, named for the venues, I'd like to create
lists that others can use to record whether a specific ticket is sold. For
example, if "Townsville" gets tickets 250 to 1200, this would be recorded
on
the Parent list and the Townsville worksheet would only list those
numbers -
with no blank rows! I may need to amend the master list from time to time-
e.g. if tickets aren't sold in Townsville, they can be re-assigned to
Cityville (and so disappear from the former worksheet and show up in the
latter).

I think I need MATCH, INDEX and\or ROW to do this, but after trying
sevreal
options, I'm no closer.

Any ideas? Thanks in anticipation.

Here is another formula solution which might work:

Try this:

=INDEX(Master!A$1:A$11,SMALL(IF(Master!$A$1:$A$11= $A$1,ROW($1:$11)*(Master!$A$1:$A$11=$A$1),""),ROW( A1)))

This array formula uses the content of cell A1 on a "child" sheet to bring
in all data for the child group.

Enter this on the second row of the child sheet and copy it down and over as
far as desired. This formula assumes that the entry on the master sheet
which indicated which items are to be brought over are located in column A.
Adjust the ranges down to match the extent of your data, ie. changen the A11
and $11 references to extend down as needed.

To make it an array press Shift+Ctrl+Enter to enter the formula.

When the formula extends beyond the maximumn number of items it will return
a #NUM! error. You can suppress this using conditional formatting or by
modifying the formula:

1. Conditional Formatting - Use =ISERR(A2) and set the font color to match
the background - white for example.

2. The modified formula approach uses:

=IF(ROW()MAX(ROW($1:$11)*(Master!$A$1:$A$11=$A$1) ),"",INDEX(Master!A$1:A$11,SMALL(IF(Master!$A$1:$A $11=$A$1,ROW($1:$11)*(Master!$A$1:$A$11=$A$1),""), ROW(A1))))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Vibeke" wrote:

Hi,
I have a master worksheet which consists of
Column A1:A5000 is numbers 1-5,000 (being ID numbers on tickets)
Column B1:B5000 is text (being one of six place names, venues for ticket
sales)

In six other (child) worksheets, named for the venues, I'd like to create
lists that others can use to record whether a specific ticket is sold. For
example, if "Townsville" gets tickets 250 to 1200, this would be recorded on
the Parent list and the Townsville worksheet would only list those numbers -
with no blank rows! I may need to amend the master list from time to time-
e.g. if tickets aren't sold in Townsville, they can be re-assigned to
Cityville (and so disappear from the former worksheet and show up in the
latter).

I think I need MATCH, INDEX and\or ROW to do this, but after trying sevreal
options, I'm no closer.

Any ideas? Thanks in anticipation.