If I've understood your questions correctly, I think the answer is to
take a step back and view this as a database problem.

What you really have going on are three or four "tables"

table 1: Hospitals
table 2: Machines
table 3: Machines in Hospitals (optional)
table 4: Hospital, Machine Readings

separating the data like this will help with data integrity and in your
case, back end analysis

So let's assume a universe of 3 hospitals and 4 machines, where every
hospital has each of the 4 machines.

So, table 1 has 3 elements, table 2 has 4 elements and table 3 has 12
elements.
table1:
Hospital A
Hospital B
Hospital C

table2:
Machine 1
Machine 2
Machine 3
Machine 4

Table 3, is a composite table composed of elements from table 1 and
table 2. You can enforce referential integrity ("RI") by using data
validations to restrict the entries in table 3 to entries in table 1
and table 2. You'll want to have an extra column to identify each
unique hospital/machine combination. This is known as a primary key.

table3:
1 Hospital A Machine 1
2 Hospital A Machine 2
3 Hospital A Machine 3
4 Hospital A Machine 4
..
..
..
12 Hospital C Machine 4


Now table 4 is composed of elements from table 3 and readings entered
manually. Again you can enforce RI with data validations. In this
case, you'll need to use the ID# for the hospital, machine combo for
the reading in question

table 4:
1 12.65
3 1.45
7 4.6
8 25.9

3, in the example above refers to Hospital A, Machine 3. You may want
to add dates to table 4 is there is a time series of readings.

Now, on the back end, you want drop downs to refer to table 1 (for
hospitals). Thus, you will be guaranteed a unique list in your drop
down. The result of the drop down selection, of course, would perform
analysis on table 4 to get some useful answers.

Hope this helps,

Ryan

warburger wrote:
> Hello,
>
> I'm having some trouble regarding 'same entries' selection...
>
> I.E. I have the following data
>
> List of Hospitals Machine Used Machine Readings
>
> Hospital A Roche Analyzer 12.65
> Hospital B Abbot Analyzer 25.9
> Hospital C Vitros Analyzer 1.45
> Hospital A Dimension Analyzer 3.6
>
> I've structured a Report Form that contains a drop-down list for
> selecting the hospital, then upon selection (let's say Hospital B), the
> machine used and the machine reading will appear in the form (Abbot and
> 25.9). This works out great for Hospitals B and C..but not with
> A....Upon selection of the first Hospital A (though in the drop-down
> list, the other Hospital A is at the bottom, the values for it will
> appear (Roche and 12.65). However, upon selection of the second
> Hospital A, the same values appear (First Hospital A values) and not
> its corresponding data (Dimension and 3.6)....It's like it cannot
> recognize another entry bearing the same name....
>
> Is there a way around this without renaming the second Hospital A to
> "Hospital A (2)"? I want it to be named exactly as it is named (which
> is Hospital A).
>
> Many thanks,
>
> lex
>
>
> --
> warburger
> ------------------------------------------------------------------------
> warburger's Profile: http://www.excelforum.com/member.php...o&userid=32387
> View this thread: http://www.excelforum.com/showthread...hreadid=568645