A
AUCP03
I have a workbook with three spreadsheets that is getting to large to manage
and I would like to automate it as much as possible. I have been tinkering
with this for a while with no success. I apologize for the wall of text
here, but I am just trying to provide all the info which might be needed.
Worksheet #1 (Called Tracker)
This worksheet has data entered in a list (Excel 2003). The headers are in
row 2 and the data starts in row 3 column A. I am only interested in the
data in the first three columns A, B, & C.
Column A Data
These cells are numbers
Column B Data
These cells are peoples names
Column C Data
These cells are dates (mm/dd/yyyy)
Worksheet #2 (Called Calendar)
This worksheet is set up as a calendar and ultimately where I want to
populate the data from Tracker. The dates are in columns B-H and J-P
(Sun-Sat twice with a space column in the middle). They are not in any
particular row, but they start in row #4. After row #4 the dates appear in
no particular order with respect to what row they are in next. The dates do
whoever appear in a rows that span both weeks.
Worksheet #3 (Called Ghost)
This sheet I copied the first 900 rows of A:C of Tracker and pasted it in
A3. This makes it so as the Tracker sheet has new rows added the Ghost sheet
is “updatedâ€, I picked 900 because I won’t ever exceed that many row entrees
in a single year. On this sheet I manipulate some of the information in
columns in A:C.
Column E (Initials)
This column changes the value in column B to initials established in
K$4:K$15 there are 13 names in all. (hence the Index instead of IF
statements)
=INDEX(K$4:K$15,MATCH(B3,{"Bob","Brian","Ryan","David","Mike","Tom","Kim","Lary","Rusty","Emily","Tod","George"},0)
Initials
K$4:K$15 is BA BB RC DD ME TF KG LH RI EJ TK TL GM
Column F (Combined Info)
This column combines column A and column E following this formula
=E3&" - "&A3
So the cells look like BA - 01-004-09
Now what I want to do is take data from Ghost and populate Calendar with it.
I want to take the cells in column C (Date) and use that to move the cells
in column F (Combined Info) of Ghost to populate the next empty cell under
the cell containing the same date in Calendar.
So I want to take data from Tracker to Ghost, change the data within ghost,
then take the data from Ghost and move it to the corresponding date in
Calendar.
All this moving from Ghost to Calendar is done manually and I would like to
automate it if possible. Any suggestions on how to do this and to limit the
size of this document? I think the size is coming from the duplication of
information occurring. Thank you.
and I would like to automate it as much as possible. I have been tinkering
with this for a while with no success. I apologize for the wall of text
here, but I am just trying to provide all the info which might be needed.
Worksheet #1 (Called Tracker)
This worksheet has data entered in a list (Excel 2003). The headers are in
row 2 and the data starts in row 3 column A. I am only interested in the
data in the first three columns A, B, & C.
Column A Data
These cells are numbers
Column B Data
These cells are peoples names
Column C Data
These cells are dates (mm/dd/yyyy)
Worksheet #2 (Called Calendar)
This worksheet is set up as a calendar and ultimately where I want to
populate the data from Tracker. The dates are in columns B-H and J-P
(Sun-Sat twice with a space column in the middle). They are not in any
particular row, but they start in row #4. After row #4 the dates appear in
no particular order with respect to what row they are in next. The dates do
whoever appear in a rows that span both weeks.
Worksheet #3 (Called Ghost)
This sheet I copied the first 900 rows of A:C of Tracker and pasted it in
A3. This makes it so as the Tracker sheet has new rows added the Ghost sheet
is “updatedâ€, I picked 900 because I won’t ever exceed that many row entrees
in a single year. On this sheet I manipulate some of the information in
columns in A:C.
Column E (Initials)
This column changes the value in column B to initials established in
K$4:K$15 there are 13 names in all. (hence the Index instead of IF
statements)
=INDEX(K$4:K$15,MATCH(B3,{"Bob","Brian","Ryan","David","Mike","Tom","Kim","Lary","Rusty","Emily","Tod","George"},0)
Initials
K$4:K$15 is BA BB RC DD ME TF KG LH RI EJ TK TL GM
Column F (Combined Info)
This column combines column A and column E following this formula
=E3&" - "&A3
So the cells look like BA - 01-004-09
Now what I want to do is take data from Ghost and populate Calendar with it.
I want to take the cells in column C (Date) and use that to move the cells
in column F (Combined Info) of Ghost to populate the next empty cell under
the cell containing the same date in Calendar.
So I want to take data from Tracker to Ghost, change the data within ghost,
then take the data from Ghost and move it to the corresponding date in
Calendar.
All this moving from Ghost to Calendar is done manually and I would like to
automate it if possible. Any suggestions on how to do this and to limit the
size of this document? I think the size is coming from the duplication of
information occurring. Thank you.