J
josephrowan
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
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