W
Walt
Using Microsoft Excel XP, I have created a workbook consisting of
approximately 30 tabs. The workbook is designed to keep track of a project {
The Flags for Patriotism Program } for a social organization.
Briefly, the Flags Program is a fund-raising project for the organization in
which members of the organization post American Flags at participating
businesses on certain holidays throughout the year. The service costs $50 to
the business for a year. The Flags Program consists of 120 Flag Accounts,
divided into 6 different routes around town.
Now, for my problem...
As I said, there are approximately 30 tabs in the workbook. Six of those
tabs are the 'Route Sheets', named "R-A", "R-B", "R-C", "R-D", "R-E", and
"R-F", and one of those tabs, the 'Summary Sheet' is named "Sum". The 'Route
Sheets' are identical in their set-up and lay-out. The only difference in
them is the names and addresses of the participating businesses on each route.
Each of the 'Route Sheets' contains four cells where the people(s) names
that posted that paprticular route can be manually entered, one cell that
counts the number of names and puts a numeric value in, and one cell for
manually putting a vehicle number in.
By comparing the vehicle numbers of each route and listing the numerci value
of the number of people that helped, I can get the NUMERIC value to work out
to the exact number of people that helped on the entire project for the day.
What I cannot get to work is the list of names to not be repeated. In other
words, 99.99% of the time, Robin will post two of the routes { "R-A" and
"R-B" }. Therefore, his name will be listed two times - once on each
individual 'Route Sheet'. On the 'Summary Page' is an area to list the names
of the people that helped with the project. Because Robin posted two routes,
it always lists his name twice. On a typical posting date, the data would
look something like this:
Tab "R-A" - Cell A4 = R. H. - Cell K4 = M. C.
Tab "R-A" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-A" - Cell U5 = 2 { Two people posted this route. }
Tab "R-A" - Cell AA5 = 1 { Manually assigned vehicle number. }
Tab "R-B" - Cell A4 = R. H. - Cell K4 = M. C.
Tab "R-B" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-B" - Cell U5 = 2 { Two people posted this route. }
Tab "R-B" - Cell AA5 = 1 { Manually assigned vehicle number. }
Tab "R-C" - Cell A4 = W. A. - Cell K4 = C. C.
Tab "R-C" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-C" - Cell U5 = 2 { Two people posted this route. }
Tab "R-C" - Cell AA5 = 2 { Manually assigned vehicle number. }
Tab "R-D" - Cell A4 = W. A. - Cell K4 = C. C.
Tab "R-D" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-D" - Cell U5 = 2 { Two people posted this route. }
Tab "R-D" - Cell AA5 = 2 { Manually assigned vehicle number. }
Tab "R-E" - Cell A4 = L. C. - Cell K4 = E. O.
Tab "R-E" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-E" - Cell U5 = 2 { Two people posted this route. }
Tab "R-E" - Cell AA5 = 3 { Manually assigned vehicle number. }
Tab "R-F" - Cell A4 = L. C. - Cell K4 = E. O.
Tab "R-F" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-F" - Cell U5 = 2 { Two people posted this route. }
Tab "R-F" - Cell AA5 = 3 { Manually assigned vehicle number. }
As you can see, in this example, there are a total of three (3) vehicles
helping on a particular posting. Each vehicle has a total of two (2) people.
Therefore, there is a total of six (6) people involved with the project on
this particular holiday.
On the 'Summary Page' there is a section { Cells A30:AW35 } to keep track
of manpower. As I said earlier, NUMERICALLY I can get the numbers to work by
comparing vehicle numbers with the [Bold]counted[/Bold] manpower on each
route, etc. However, I cannot seem to get the TEXT of the manpower (the
people's names) to work the same way. Everything I have tried will list each
of the people above two times because they posted two routes. Obviously, "R.
H." is only one person (as are all the others), and I would like to find how
to tell Excel to recognize that "R. H." is the same TEXT and therefore only
list it one time on the 'Summary Page' cells for the manpower.
I have tried to go into 'Data > Filters > Advanced Filters' and filtering
the data for 'Unique Records', and this almost worked. The first problem
that I found is that it still counted "R. H." two times, and at the end of
the list, it showed a "0", which threw the count off. The second problem
that I found is that if I decide to add/delete a name to/from a 'Route
Sheet', it does not automatically update itself. I did NOT try to add/delete
a name from the 'Route Sheet' and then close the program and re-open the
program to see if it would update itself because, well, who wants to update
information and have to close/re-open the program every time just to get it
to update the information for printing and accurate information tracking?
I don't know if it will make a difference or not (as far as I can tell, it
shouldn't), but, EVERY page in the workbook is formatted so that EVERY column
on the page is at 0.92 wide. The reason for this is that I can merge cells
in a row to preserve the layout of the forms. In other words, on one row I
may need a cell that is 15 wide, and on the next row, I may need a cell that
is only 5 wide. Because an individual cell cannot be different widths from
the same cell in the rows above it/below it, I need to be able to merge
multiple cells on a row to get the area for data to the correct width for
what I need without effecting the same cell width above/below it that is
(most likely) a different width.
Any help anyone can give will be appreciated. And, THANK YOU in advance for
any help you can give.
Walt
approximately 30 tabs. The workbook is designed to keep track of a project {
The Flags for Patriotism Program } for a social organization.
Briefly, the Flags Program is a fund-raising project for the organization in
which members of the organization post American Flags at participating
businesses on certain holidays throughout the year. The service costs $50 to
the business for a year. The Flags Program consists of 120 Flag Accounts,
divided into 6 different routes around town.
Now, for my problem...
As I said, there are approximately 30 tabs in the workbook. Six of those
tabs are the 'Route Sheets', named "R-A", "R-B", "R-C", "R-D", "R-E", and
"R-F", and one of those tabs, the 'Summary Sheet' is named "Sum". The 'Route
Sheets' are identical in their set-up and lay-out. The only difference in
them is the names and addresses of the participating businesses on each route.
Each of the 'Route Sheets' contains four cells where the people(s) names
that posted that paprticular route can be manually entered, one cell that
counts the number of names and puts a numeric value in, and one cell for
manually putting a vehicle number in.
By comparing the vehicle numbers of each route and listing the numerci value
of the number of people that helped, I can get the NUMERIC value to work out
to the exact number of people that helped on the entire project for the day.
What I cannot get to work is the list of names to not be repeated. In other
words, 99.99% of the time, Robin will post two of the routes { "R-A" and
"R-B" }. Therefore, his name will be listed two times - once on each
individual 'Route Sheet'. On the 'Summary Page' is an area to list the names
of the people that helped with the project. Because Robin posted two routes,
it always lists his name twice. On a typical posting date, the data would
look something like this:
Tab "R-A" - Cell A4 = R. H. - Cell K4 = M. C.
Tab "R-A" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-A" - Cell U5 = 2 { Two people posted this route. }
Tab "R-A" - Cell AA5 = 1 { Manually assigned vehicle number. }
Tab "R-B" - Cell A4 = R. H. - Cell K4 = M. C.
Tab "R-B" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-B" - Cell U5 = 2 { Two people posted this route. }
Tab "R-B" - Cell AA5 = 1 { Manually assigned vehicle number. }
Tab "R-C" - Cell A4 = W. A. - Cell K4 = C. C.
Tab "R-C" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-C" - Cell U5 = 2 { Two people posted this route. }
Tab "R-C" - Cell AA5 = 2 { Manually assigned vehicle number. }
Tab "R-D" - Cell A4 = W. A. - Cell K4 = C. C.
Tab "R-D" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-D" - Cell U5 = 2 { Two people posted this route. }
Tab "R-D" - Cell AA5 = 2 { Manually assigned vehicle number. }
Tab "R-E" - Cell A4 = L. C. - Cell K4 = E. O.
Tab "R-E" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-E" - Cell U5 = 2 { Two people posted this route. }
Tab "R-E" - Cell AA5 = 3 { Manually assigned vehicle number. }
Tab "R-F" - Cell A4 = L. C. - Cell K4 = E. O.
Tab "R-F" - Cell A5 = Blank - Cell K5 = Blank
Tab "R-F" - Cell U5 = 2 { Two people posted this route. }
Tab "R-F" - Cell AA5 = 3 { Manually assigned vehicle number. }
As you can see, in this example, there are a total of three (3) vehicles
helping on a particular posting. Each vehicle has a total of two (2) people.
Therefore, there is a total of six (6) people involved with the project on
this particular holiday.
On the 'Summary Page' there is a section { Cells A30:AW35 } to keep track
of manpower. As I said earlier, NUMERICALLY I can get the numbers to work by
comparing vehicle numbers with the [Bold]counted[/Bold] manpower on each
route, etc. However, I cannot seem to get the TEXT of the manpower (the
people's names) to work the same way. Everything I have tried will list each
of the people above two times because they posted two routes. Obviously, "R.
H." is only one person (as are all the others), and I would like to find how
to tell Excel to recognize that "R. H." is the same TEXT and therefore only
list it one time on the 'Summary Page' cells for the manpower.
I have tried to go into 'Data > Filters > Advanced Filters' and filtering
the data for 'Unique Records', and this almost worked. The first problem
that I found is that it still counted "R. H." two times, and at the end of
the list, it showed a "0", which threw the count off. The second problem
that I found is that if I decide to add/delete a name to/from a 'Route
Sheet', it does not automatically update itself. I did NOT try to add/delete
a name from the 'Route Sheet' and then close the program and re-open the
program to see if it would update itself because, well, who wants to update
information and have to close/re-open the program every time just to get it
to update the information for printing and accurate information tracking?
I don't know if it will make a difference or not (as far as I can tell, it
shouldn't), but, EVERY page in the workbook is formatted so that EVERY column
on the page is at 0.92 wide. The reason for this is that I can merge cells
in a row to preserve the layout of the forms. In other words, on one row I
may need a cell that is 15 wide, and on the next row, I may need a cell that
is only 5 wide. Because an individual cell cannot be different widths from
the same cell in the rows above it/below it, I need to be able to merge
multiple cells on a row to get the area for data to the correct width for
what I need without effecting the same cell width above/below it that is
(most likely) a different width.
Any help anyone can give will be appreciated. And, THANK YOU in advance for
any help you can give.
Walt