Hi,

I track the population in a large homeless shelter (total capacity
1,000). As I fill the beds for the eveing, I often need a reminder that
a particular bed is not available as I am giving out beds that are
available.

In this case, a letter indicates the dorm, a number indicates the bed.
At the beginning of the day, I enter my occupied beds into columns then
record on a second sheet the persons who we are taking in for the night.


(For simplicity, I am using a small range of data to demonstrate)

In a workbook, I have a range of data in columns on Sheet 1. The column
headings (Dorms) are A through C and the data (Beds) runs from 1 to 10
in each column. This grid represents the occupied beds in each
dormitory.

A B C
3 2 1
5 4 2
7 9 8

From the data above, A3, A5, A7, B2, B4, B9, C1, C2 and C8 are beds are
already occupied.

On Sheet 2 in the workbook, there are a series of rows that indicate
the incoming person who received a particular bed. Along with Name, Age
and ID cells in each row are two cells; in the first cell I would enter
the letter A, B, or C (Dorm). In the next cell, I would enter a number
1 to 100 (Bed).

The rows looks like this:
Last First DOB ID Dorm Bed
Doe John 1/1/49 12345 A 3


The task is this: After adding numbers to the columns on the first
page, I would like to receive an error message should I enter matching
information on the second page. Using the info above, entering an "A"
in the Dorm cell, then entering a "3" in the Bed cell would produce the
warning ("Bed Occupied" or something like that). Entering an "A" then
"1" would not produce any message.

Ideally I would like this to be an event triggered when the two "row
cells" combine to match the column heading and a number it its data
range. Possibly done with VBA.

Thoughts?

Thanks

Joe


--
josephrowan
------------------------------------------------------------------------
josephrowan's Profile: http://www.excelforum.com/member.php...o&userid=14407
View this thread: http://www.excelforum.com/showthread...hreadid=389676

Try a formula approach ?

You have in Sheet1, cols A to C,
headers: A, B, C in A1:C1 and data from row2 down

A B C
3 2 1
5 4 2
7 9 8

In Sheet2, this table is in cols A to F,
headers in A1:F1, data from row2 down

Last First DOB ID Dorm Bed
Doe John 1/1/49 12345 A 3

Let's use an adjacent col G to provide the check status for each row

Put a label, say: "Check" in G1

Put in G2, array-enter the formula (press CTRL+SHIFT+ENTER):

=IF(ISNUMBER(MATCH(F2,OFFSET(Sheet1!$A:$A,,MATCH(E 2,Sheet1!$1:$1,0)-1),0)),"
Bed Occupied !!","")

Copy G2 down as far as required

Col G will indicate :"Bed Occupied !!" if the inputs in cols E and F match
with what's in Sheet1. If everything's cool, col G will remain blank.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"josephrowan"
wrote in message
...

Hi,

I track the population in a large homeless shelter (total capacity
1,000). As I fill the beds for the eveing, I often need a reminder that
a particular bed is not available as I am giving out beds that are
available.

In this case, a letter indicates the dorm, a number indicates the bed.
At the beginning of the day, I enter my occupied beds into columns then
record on a second sheet the persons who we are taking in for the night.


(For simplicity, I am using a small range of data to demonstrate)

In a workbook, I have a range of data in columns on Sheet 1. The column
headings (Dorms) are A through C and the data (Beds) runs from 1 to 10
in each column. This grid represents the occupied beds in each
dormitory.

A B C
3 2 1
5 4 2
7 9 8

From the data above, A3, A5, A7, B2, B4, B9, C1, C2 and C8 are beds are
already occupied.

On Sheet 2 in the workbook, there are a series of rows that indicate
the incoming person who received a particular bed. Along with Name, Age
and ID cells in each row are two cells; in the first cell I would enter
the letter A, B, or C (Dorm). In the next cell, I would enter a number
1 to 100 (Bed).

The rows looks like this:
Last First DOB ID Dorm Bed
Doe John 1/1/49 12345 A 3


The task is this: After adding numbers to the columns on the first
page, I would like to receive an error message should I enter matching
information on the second page. Using the info above, entering an "A"
in the Dorm cell, then entering a "3" in the Bed cell would produce the
warning ("Bed Occupied" or something like that). Entering an "A" then
"1" would not produce any message.

Ideally I would like this to be an event triggered when the two "row
cells" combine to match the column heading and a number it its data
range. Possibly done with VBA.

Thoughts?

Thanks

Joe


--
josephrowan
------------------------------------------------------------------------
josephrowan's Profile:
http://www.excelforum.com/member.php...o&userid=14407
View this thread: http://www.excelforum.com/showthread...hreadid=389676

Max,

That works great. No need to get into any weird VBA with something so
simple. I should post here more often!

Thanks again

Joe


--
josephrowan
------------------------------------------------------------------------
josephrowan's Profile: http://www.excelforum.com/member.php...o&userid=14407
View this thread: http://www.excelforum.com/showthread...hreadid=389676

Glad to hear that!
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"josephrowan"
wrote in message
...

Max,

That works great. No need to get into any weird VBA with something so
simple. I should post here more often!

Thanks again

Joe