Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 28th 04, 09:25 PM
FRUSTRATED
 
Posts: n/a
Default HELP! How do you--> Lock a set of rows but also link worksheets to

Ok, let me explain. I'm on a military base here and I am making (trying to
atleast) a WORKBOOK that can keep track of who is on LEAVE and who is NOT ON
LEAVE. It is a very simple spread sheet to keep track of who should be here
or on vacation... as follows:

WORKSHEET 1: Is called "MASTER" and it would contain the list of everyone on
base as such...
Column A: (Last, First, Middle RANK) Their name and title
Columns B thru AF: (31 columns) indicating the amount of days in a month
(excluding February-28, April-30, etc...)
Basically, in those 31 columns (corresponding to the rows next to each
persons names) I would put an "X" for everyday that they would be on LEAVE
(vacation) and an empty slot would indicate that they should be on base
somewhere,... working (supposedly).

WORKSHEETS 2-13: (JAN thru DEC) would have the SAME info as the MASTER
worksheet except it would all be "future" data of when that person/people may
be on vacation or on duty that day. Make sense so far???

Theoretically if this all worked I could look at a month (worksheet) and
date (column) for the corresponding name (row) and know if that person is on
base or on Leave (vacation). TADA! =)



PROBLEM 1:
I created the MASTER worksheet to link to the other 12 worksheets (Janurary
thru December) so that when a new person arrives on base, I can enter their
name in the Master worksheet and "Sort A-Z" it would (should) "automatically"
update all the "Column A's" and corresponding rows for each name in all the
other worksheets (Janurary thru December). Unfortunately after I use the
"Sort A-Z" function it doesn't move the corresponding rows associated to that
name with the sort function.

Question for Problem 1:
How do I lock the MASTER Worksheet "rows" to their corresponding names so
that if I were to enter a new name and filter (Sort A-Z) wouldn't JUST sort
the names but also the corresponding rows to their names.



Problem 2:
I know how to link worksheets together... (my MASTER worksheet to all the
other 12 months worksheets) but I'm not sure if I'm doing it right. I'm
copying from my MASTER and using the "Paste Special - Paste Link" onto the
other 12 worksheets for Jan-Dec.

Question for Problem 2:
Is there a better method? When I use the Paste Link method it shows a bunch
of "0's" in the empty boxes which is producing a lot of clutter and
confusion. Is there a way to link the worksheets together to the Master
worksheet without having all those "0's"? <this workbook/worksheet must be
soldier/sailor proof...if you know what i mean


I hope someone out there knows what I am talking about...and knows the
answers to my frustrating situation...


Truely Greatful,

"Frustrated"

PS. None of the discussion links had the answers I was looking for.





--
"I should have paid attention in computer class..."

  #2   Report Post  
Old December 29th 04, 02:29 PM
IanRoy
 
Posts: n/a
Default

Hello FRUSTRATED,

There is a simpler way. You can group sheets so that you can enter data into
all sheets at once. Click the first tab, then shift+click the last tab. Then
enter your servicemembers names etc., (or paste them from your current
master). Click a non-bolded tab to ungroup sheets before entering monthly
data. To add new records, group sheets as before, and insert as many rows as
you will need (to prevent accidental overwriting of data). (To insert a row,
right-click a row number and choose "Insert.") If you need to sort, ungroup
the sheets, click the small rectangle at the intersection of the column
letters and row numbers to select the entire sheet, then perform the sort. Do
the same for the next sheet and so on.

If you need to protect data from accidental tampering, you'll find options
at ToolsProtection.

Best Wishes,

IanRoy


"FRUSTRATED" wrote:

Ok, let me explain. I'm on a military base here and I am making (trying to
atleast) a WORKBOOK that can keep track of who is on LEAVE and who is NOT ON
LEAVE. It is a very simple spread sheet to keep track of who should be here
or on vacation... as follows:

WORKSHEET 1: Is called "MASTER" and it would contain the list of everyone on
base as such...
Column A: (Last, First, Middle RANK) Their name and title
Columns B thru AF: (31 columns) indicating the amount of days in a month
(excluding February-28, April-30, etc...)
Basically, in those 31 columns (corresponding to the rows next to each
persons names) I would put an "X" for everyday that they would be on LEAVE
(vacation) and an empty slot would indicate that they should be on base
somewhere,... working (supposedly).

WORKSHEETS 2-13: (JAN thru DEC) would have the SAME info as the MASTER
worksheet except it would all be "future" data of when that person/people may
be on vacation or on duty that day. Make sense so far???

Theoretically if this all worked I could look at a month (worksheet) and
date (column) for the corresponding name (row) and know if that person is on
base or on Leave (vacation). TADA! =)



PROBLEM 1:
I created the MASTER worksheet to link to the other 12 worksheets (Janurary
thru December) so that when a new person arrives on base, I can enter their
name in the Master worksheet and "Sort A-Z" it would (should) "automatically"
update all the "Column A's" and corresponding rows for each name in all the
other worksheets (Janurary thru December). Unfortunately after I use the
"Sort A-Z" function it doesn't move the corresponding rows associated to that
name with the sort function.

Question for Problem 1:
How do I lock the MASTER Worksheet "rows" to their corresponding names so
that if I were to enter a new name and filter (Sort A-Z) wouldn't JUST sort
the names but also the corresponding rows to their names.



Problem 2:
I know how to link worksheets together... (my MASTER worksheet to all the
other 12 months worksheets) but I'm not sure if I'm doing it right. I'm
copying from my MASTER and using the "Paste Special - Paste Link" onto the
other 12 worksheets for Jan-Dec.

Question for Problem 2:
Is there a better method? When I use the Paste Link method it shows a bunch
of "0's" in the empty boxes which is producing a lot of clutter and
confusion. Is there a way to link the worksheets together to the Master
worksheet without having all those "0's"? <this workbook/worksheet must be
soldier/sailor proof...if you know what i mean


I hope someone out there knows what I am talking about...and knows the
answers to my frustrating situation...


Truely Greatful,

"Frustrated"

PS. None of the discussion links had the answers I was looking for.





--
"I should have paid attention in computer class..."

  #3   Report Post  
Old December 29th 04, 05:57 PM
FRUSTRATED
 
Posts: n/a
Default

IanRoy!

It works upto a certain degree but won't let me "Sort A-Z" and even if you
"un-shift" all the tabs... and you "Sort A-Z" it won't shift the
'corresponding rows' with the people they belong to... that's the tricky part.

I have to make this as much soldier/sailor proof as possible...
Anymore options?


"IanRoy" wrote:

Hello FRUSTRATED,

There is a simpler way. You can group sheets so that you can enter data into
all sheets at once. Click the first tab, then shift+click the last tab. Then
enter your servicemembers names etc., (or paste them from your current
master). Click a non-bolded tab to ungroup sheets before entering monthly
data. To add new records, group sheets as before, and insert as many rows as
you will need (to prevent accidental overwriting of data). (To insert a row,
right-click a row number and choose "Insert.") If you need to sort, ungroup
the sheets, click the small rectangle at the intersection of the column
letters and row numbers to select the entire sheet, then perform the sort. Do
the same for the next sheet and so on.

If you need to protect data from accidental tampering, you'll find options
at ToolsProtection.

Best Wishes,

IanRoy


"FRUSTRATED" wrote:

Ok, let me explain. I'm on a military base here and I am making (trying to
atleast) a WORKBOOK that can keep track of who is on LEAVE and who is NOT ON
LEAVE. It is a very simple spread sheet to keep track of who should be here
or on vacation... as follows:

WORKSHEET 1: Is called "MASTER" and it would contain the list of everyone on
base as such...
Column A: (Last, First, Middle RANK) Their name and title
Columns B thru AF: (31 columns) indicating the amount of days in a month
(excluding February-28, April-30, etc...)
Basically, in those 31 columns (corresponding to the rows next to each
persons names) I would put an "X" for everyday that they would be on LEAVE
(vacation) and an empty slot would indicate that they should be on base
somewhere,... working (supposedly).

WORKSHEETS 2-13: (JAN thru DEC) would have the SAME info as the MASTER
worksheet except it would all be "future" data of when that person/people may
be on vacation or on duty that day. Make sense so far???

Theoretically if this all worked I could look at a month (worksheet) and
date (column) for the corresponding name (row) and know if that person is on
base or on Leave (vacation). TADA! =)



PROBLEM 1:
I created the MASTER worksheet to link to the other 12 worksheets (Janurary
thru December) so that when a new person arrives on base, I can enter their
name in the Master worksheet and "Sort A-Z" it would (should) "automatically"
update all the "Column A's" and corresponding rows for each name in all the
other worksheets (Janurary thru December). Unfortunately after I use the
"Sort A-Z" function it doesn't move the corresponding rows associated to that
name with the sort function.

Question for Problem 1:
How do I lock the MASTER Worksheet "rows" to their corresponding names so
that if I were to enter a new name and filter (Sort A-Z) wouldn't JUST sort
the names but also the corresponding rows to their names.



Problem 2:
I know how to link worksheets together... (my MASTER worksheet to all the
other 12 months worksheets) but I'm not sure if I'm doing it right. I'm
copying from my MASTER and using the "Paste Special - Paste Link" onto the
other 12 worksheets for Jan-Dec.

Question for Problem 2:
Is there a better method? When I use the Paste Link method it shows a bunch
of "0's" in the empty boxes which is producing a lot of clutter and
confusion. Is there a way to link the worksheets together to the Master
worksheet without having all those "0's"? <this workbook/worksheet must be
soldier/sailor proof...if you know what i mean


I hope someone out there knows what I am talking about...and knows the
answers to my frustrating situation...


Truely Greatful,

"Frustrated"

PS. None of the discussion links had the answers I was looking for.





--
"I should have paid attention in computer class..."

  #4   Report Post  
Old December 29th 04, 07:05 PM
IanRoy
 
Posts: n/a
Default

Hi FRUSTRATED,

I hope I was not too unclear. All the info for a given person should be on
the same row (and in every sheet), and not calculated by reference to another
sheet, either. My idea was to enter your personnel data into all sheets at
once (every time you need to). Then ungroup and enter your month-specific
data. I was just fiddling with my test workbook and discovered that if your
header row has no gaps, you won't need to select rows (at least in Excel
2003). Just select any cell (only one cell) in the column you wish to sort
by, and click the Sort button. This part has to be done to each sheet in turn
though, with the sheets ungrouped. To make it "soldier/sailor proof," I
suggest protecting both the sheets and the workbook, and only unlocking those
cells your users may need to be able to edit. In my sad experience, a
spreadsheet that is not under the control/responsibility of some one person
can quickly become a mess.

Regards,
IanRoy.

"FRUSTRATED" wrote:

IanRoy!

It works upto a certain degree but won't let me "Sort A-Z" and even if you
"un-shift" all the tabs... and you "Sort A-Z" it won't shift the
'corresponding rows' with the people they belong to... that's the tricky part.

I have to make this as much soldier/sailor proof as possible...
Anymore options?


"IanRoy" wrote:

Hello FRUSTRATED,

There is a simpler way. You can group sheets so that you can enter data into
all sheets at once. Click the first tab, then shift+click the last tab. Then
enter your servicemembers names etc., (or paste them from your current
master). Click a non-bolded tab to ungroup sheets before entering monthly
data. To add new records, group sheets as before, and insert as many rows as
you will need (to prevent accidental overwriting of data). (To insert a row,
right-click a row number and choose "Insert.") If you need to sort, ungroup
the sheets, click the small rectangle at the intersection of the column
letters and row numbers to select the entire sheet, then perform the sort. Do
the same for the next sheet and so on.

If you need to protect data from accidental tampering, you'll find options
at ToolsProtection.

Best Wishes,

IanRoy


"FRUSTRATED" wrote:

Ok, let me explain. I'm on a military base here and I am making (trying to
atleast) a WORKBOOK that can keep track of who is on LEAVE and who is NOT ON
LEAVE. It is a very simple spread sheet to keep track of who should be here
or on vacation... as follows:

WORKSHEET 1: Is called "MASTER" and it would contain the list of everyone on
base as such...
Column A: (Last, First, Middle RANK) Their name and title
Columns B thru AF: (31 columns) indicating the amount of days in a month
(excluding February-28, April-30, etc...)
Basically, in those 31 columns (corresponding to the rows next to each
persons names) I would put an "X" for everyday that they would be on LEAVE
(vacation) and an empty slot would indicate that they should be on base
somewhere,... working (supposedly).

WORKSHEETS 2-13: (JAN thru DEC) would have the SAME info as the MASTER
worksheet except it would all be "future" data of when that person/people may
be on vacation or on duty that day. Make sense so far???

Theoretically if this all worked I could look at a month (worksheet) and
date (column) for the corresponding name (row) and know if that person is on
base or on Leave (vacation). TADA! =)



PROBLEM 1:
I created the MASTER worksheet to link to the other 12 worksheets (Janurary
thru December) so that when a new person arrives on base, I can enter their
name in the Master worksheet and "Sort A-Z" it would (should) "automatically"
update all the "Column A's" and corresponding rows for each name in all the
other worksheets (Janurary thru December). Unfortunately after I use the
"Sort A-Z" function it doesn't move the corresponding rows associated to that
name with the sort function.

Question for Problem 1:
How do I lock the MASTER Worksheet "rows" to their corresponding names so
that if I were to enter a new name and filter (Sort A-Z) wouldn't JUST sort
the names but also the corresponding rows to their names.



Problem 2:
I know how to link worksheets together... (my MASTER worksheet to all the
other 12 months worksheets) but I'm not sure if I'm doing it right. I'm
copying from my MASTER and using the "Paste Special - Paste Link" onto the
other 12 worksheets for Jan-Dec.

Question for Problem 2:
Is there a better method? When I use the Paste Link method it shows a bunch
of "0's" in the empty boxes which is producing a lot of clutter and
confusion. Is there a way to link the worksheets together to the Master
worksheet without having all those "0's"? <this workbook/worksheet must be
soldier/sailor proof...if you know what i mean


I hope someone out there knows what I am talking about...and knows the
answers to my frustrating situation...


Truely Greatful,

"Frustrated"

PS. None of the discussion links had the answers I was looking for.





--
"I should have paid attention in computer class..."

  #5   Report Post  
Old December 29th 04, 07:07 PM
IanRoy
 
Posts: n/a
Default

Hi FRUSTRATED,

I hope I was not too unclear. All the info for a given person should be on
the same row (and in every sheet), and not calculated by reference to another
sheet, either. My idea was to enter your personnel data into all sheets at
once (every time you need to). Then ungroup and enter your month-specific
data. I was just fiddling with my test workbook and discovered that if your
header row has no gaps, you won't need to select rows (at least in Excel
2003). Just select any cell (only one cell) in the column you wish to sort
by, and click the Sort button. This part has to be done to each sheet in turn
though, with the sheets ungrouped. To make it "soldier/sailor proof," I
suggest protecting both the sheets and the workbook, and only unlocking those
cells your users may need to be able to edit. In my sad experience, a
spreadsheet that is not under the control/responsibility of some one person
can quickly become a mess.

Regards,
IanRoy.


"FRUSTRATED" wrote:

IanRoy!

It works upto a certain degree but won't let me "Sort A-Z" and even if you
"un-shift" all the tabs... and you "Sort A-Z" it won't shift the
'corresponding rows' with the people they belong to... that's the tricky part.

I have to make this as much soldier/sailor proof as possible...
Anymore options?


"IanRoy" wrote:

Hello FRUSTRATED,

There is a simpler way. You can group sheets so that you can enter data into
all sheets at once. Click the first tab, then shift+click the last tab. Then
enter your servicemembers names etc., (or paste them from your current
master). Click a non-bolded tab to ungroup sheets before entering monthly
data. To add new records, group sheets as before, and insert as many rows as
you will need (to prevent accidental overwriting of data). (To insert a row,
right-click a row number and choose "Insert.") If you need to sort, ungroup
the sheets, click the small rectangle at the intersection of the column
letters and row numbers to select the entire sheet, then perform the sort. Do
the same for the next sheet and so on.

If you need to protect data from accidental tampering, you'll find options
at ToolsProtection.

Best Wishes,

IanRoy


"FRUSTRATED" wrote:

Ok, let me explain. I'm on a military base here and I am making (trying to
atleast) a WORKBOOK that can keep track of who is on LEAVE and who is NOT ON
LEAVE. It is a very simple spread sheet to keep track of who should be here
or on vacation... as follows:

WORKSHEET 1: Is called "MASTER" and it would contain the list of everyone on
base as such...
Column A: (Last, First, Middle RANK) Their name and title
Columns B thru AF: (31 columns) indicating the amount of days in a month
(excluding February-28, April-30, etc...)
Basically, in those 31 columns (corresponding to the rows next to each
persons names) I would put an "X" for everyday that they would be on LEAVE
(vacation) and an empty slot would indicate that they should be on base
somewhere,... working (supposedly).

WORKSHEETS 2-13: (JAN thru DEC) would have the SAME info as the MASTER
worksheet except it would all be "future" data of when that person/people may
be on vacation or on duty that day. Make sense so far???

Theoretically if this all worked I could look at a month (worksheet) and
date (column) for the corresponding name (row) and know if that person is on
base or on Leave (vacation). TADA! =)



PROBLEM 1:
I created the MASTER worksheet to link to the other 12 worksheets (Janurary
thru December) so that when a new person arrives on base, I can enter their
name in the Master worksheet and "Sort A-Z" it would (should) "automatically"
update all the "Column A's" and corresponding rows for each name in all the
other worksheets (Janurary thru December). Unfortunately after I use the
"Sort A-Z" function it doesn't move the corresponding rows associated to that
name with the sort function.

Question for Problem 1:
How do I lock the MASTER Worksheet "rows" to their corresponding names so
that if I were to enter a new name and filter (Sort A-Z) wouldn't JUST sort
the names but also the corresponding rows to their names.



Problem 2:
I know how to link worksheets together... (my MASTER worksheet to all the
other 12 months worksheets) but I'm not sure if I'm doing it right. I'm
copying from my MASTER and using the "Paste Special - Paste Link" onto the
other 12 worksheets for Jan-Dec.

Question for Problem 2:
Is there a better method? When I use the Paste Link method it shows a bunch
of "0's" in the empty boxes which is producing a lot of clutter and
confusion. Is there a way to link the worksheets together to the Master
worksheet without having all those "0's"? <this workbook/worksheet must be
soldier/sailor proof...if you know what i mean


I hope someone out there knows what I am talking about...and knows the
answers to my frustrating situation...


Truely Greatful,

"Frustrated"

PS. None of the discussion links had the answers I was looking for.





--
"I should have paid attention in computer class..."



  #6   Report Post  
Old December 29th 04, 08:29 PM
FRUSTRATED
 
Posts: n/a
Default

I totally agree with you. If you are not in full control of the
spreadsheet,...it can quickly become someone elses nightmare... and yours
when you return to it.

Is there anyone else out there who by any chance would know how to lock rows
together so that when sorting a Column...such as "Column A"... all the rows
in that column are locked together.... so Row 1 will always stay the same as
Row 1 even if it get alphabetically moved around..........???


My cubicle is getting smaller...


"IanRoy" wrote:

Hi FRUSTRATED,

I hope I was not too unclear. All the info for a given person should be on
the same row (and in every sheet), and not calculated by reference to another
sheet, either. My idea was to enter your personnel data into all sheets at
once (every time you need to). Then ungroup and enter your month-specific
data. I was just fiddling with my test workbook and discovered that if your
header row has no gaps, you won't need to select rows (at least in Excel
2003). Just select any cell (only one cell) in the column you wish to sort
by, and click the Sort button. This part has to be done to each sheet in turn
though, with the sheets ungrouped. To make it "soldier/sailor proof," I
suggest protecting both the sheets and the workbook, and only unlocking those
cells your users may need to be able to edit. In my sad experience, a
spreadsheet that is not under the control/responsibility of some one person
can quickly become a mess.

Regards,
IanRoy.


"FRUSTRATED" wrote:

IanRoy!

It works upto a certain degree but won't let me "Sort A-Z" and even if you
"un-shift" all the tabs... and you "Sort A-Z" it won't shift the
'corresponding rows' with the people they belong to... that's the tricky part.

I have to make this as much soldier/sailor proof as possible...
Anymore options?


"IanRoy" wrote:

Hello FRUSTRATED,

There is a simpler way. You can group sheets so that you can enter data into
all sheets at once. Click the first tab, then shift+click the last tab. Then
enter your servicemembers names etc., (or paste them from your current
master). Click a non-bolded tab to ungroup sheets before entering monthly
data. To add new records, group sheets as before, and insert as many rows as
you will need (to prevent accidental overwriting of data). (To insert a row,
right-click a row number and choose "Insert.") If you need to sort, ungroup
the sheets, click the small rectangle at the intersection of the column
letters and row numbers to select the entire sheet, then perform the sort. Do
the same for the next sheet and so on.

If you need to protect data from accidental tampering, you'll find options
at ToolsProtection.

Best Wishes,

IanRoy


"FRUSTRATED" wrote:

Ok, let me explain. I'm on a military base here and I am making (trying to
atleast) a WORKBOOK that can keep track of who is on LEAVE and who is NOT ON
LEAVE. It is a very simple spread sheet to keep track of who should be here
or on vacation... as follows:

WORKSHEET 1: Is called "MASTER" and it would contain the list of everyone on
base as such...
Column A: (Last, First, Middle RANK) Their name and title
Columns B thru AF: (31 columns) indicating the amount of days in a month
(excluding February-28, April-30, etc...)
Basically, in those 31 columns (corresponding to the rows next to each
persons names) I would put an "X" for everyday that they would be on LEAVE
(vacation) and an empty slot would indicate that they should be on base
somewhere,... working (supposedly).

WORKSHEETS 2-13: (JAN thru DEC) would have the SAME info as the MASTER
worksheet except it would all be "future" data of when that person/people may
be on vacation or on duty that day. Make sense so far???

Theoretically if this all worked I could look at a month (worksheet) and
date (column) for the corresponding name (row) and know if that person is on
base or on Leave (vacation). TADA! =)



PROBLEM 1:
I created the MASTER worksheet to link to the other 12 worksheets (Janurary
thru December) so that when a new person arrives on base, I can enter their
name in the Master worksheet and "Sort A-Z" it would (should) "automatically"
update all the "Column A's" and corresponding rows for each name in all the
other worksheets (Janurary thru December). Unfortunately after I use the
"Sort A-Z" function it doesn't move the corresponding rows associated to that
name with the sort function.

Question for Problem 1:
How do I lock the MASTER Worksheet "rows" to their corresponding names so
that if I were to enter a new name and filter (Sort A-Z) wouldn't JUST sort
the names but also the corresponding rows to their names.



Problem 2:
I know how to link worksheets together... (my MASTER worksheet to all the
other 12 months worksheets) but I'm not sure if I'm doing it right. I'm
copying from my MASTER and using the "Paste Special - Paste Link" onto the
other 12 worksheets for Jan-Dec.

Question for Problem 2:
Is there a better method? When I use the Paste Link method it shows a bunch
of "0's" in the empty boxes which is producing a lot of clutter and
confusion. Is there a way to link the worksheets together to the Master
worksheet without having all those "0's"? <this workbook/worksheet must be
soldier/sailor proof...if you know what i mean


I hope someone out there knows what I am talking about...and knows the
answers to my frustrating situation...


Truely Greatful,

"Frustrated"

PS. None of the discussion links had the answers I was looking for.





--
"I should have paid attention in computer class..."

  #7   Report Post  
Old December 29th 04, 10:05 PM
IanRoy
 
Posts: n/a
Default

FRUSTRATED;

Now you are frustating me! A"column" is one cell wide, it does not contain
"rows."
Row 1 is always at the top of the sheet, regardless of the sort. Sorting in
Excel is rearranging data vertically. If you select a range of cells before
performing the sort, you are telling Excel to sort only the data within that
range. Selecting everything in the sheet tells Excel to sort everything. It
defaults to sorting rows by the first column in the range, but if you use
DataSort, you can override that and sort by the column(s) of your choice. It
does not sort column A separately from B, if both are selected, it sorts both
by the first column you choose, then both by the second, and so on.

If you select only a single cell before performing the sort, Excel will make
an assumption about the intended range. If your header row (row 1: should
have name, rank, date, etc.) has no gaps, Excel 2003 will sort all the way
out to the end of the filled header row, and by the column containing the
cell you selected. This should work fine for your purpose.

The only problems I can see:
1) You are still trying to link sheets, and or use trans-sheet formulae.
2) You are selecting a range that does not include everything you want to
sort.
3) You select a single cell, but your header row has gap(s).
Any of the three would defeat your efforts.

Regards,
IanRoy


"FRUSTRATED" wrote:

I totally agree with you. If you are not in full control of the
spreadsheet,...it can quickly become someone elses nightmare... and yours
when you return to it.

Is there anyone else out there who by any chance would know how to lock rows
together so that when sorting a Column...such as "Column A"... all the rows
in that column are locked together.... so Row 1 will always stay the same as
Row 1 even if it get alphabetically moved around..........???


My cubicle is getting smaller...


"IanRoy" wrote:

Hi FRUSTRATED,

I hope I was not too unclear. All the info for a given person should be on
the same row (and in every sheet), and not calculated by reference to another
sheet, either. My idea was to enter your personnel data into all sheets at
once (every time you need to). Then ungroup and enter your month-specific
data. I was just fiddling with my test workbook and discovered that if your
header row has no gaps, you won't need to select rows (at least in Excel
2003). Just select any cell (only one cell) in the column you wish to sort
by, and click the Sort button. This part has to be done to each sheet in turn
though, with the sheets ungrouped. To make it "soldier/sailor proof," I
suggest protecting both the sheets and the workbook, and only unlocking those
cells your users may need to be able to edit. In my sad experience, a
spreadsheet that is not under the control/responsibility of some one person
can quickly become a mess.

Regards,
IanRoy.


"FRUSTRATED" wrote:

IanRoy!

It works upto a certain degree but won't let me "Sort A-Z" and even if you
"un-shift" all the tabs... and you "Sort A-Z" it won't shift the
'corresponding rows' with the people they belong to... that's the tricky part.

I have to make this as much soldier/sailor proof as possible...
Anymore options?


"IanRoy" wrote:

Hello FRUSTRATED,

There is a simpler way. You can group sheets so that you can enter data into
all sheets at once. Click the first tab, then shift+click the last tab. Then
enter your servicemembers names etc., (or paste them from your current
master). Click a non-bolded tab to ungroup sheets before entering monthly
data. To add new records, group sheets as before, and insert as many rows as
you will need (to prevent accidental overwriting of data). (To insert a row,
right-click a row number and choose "Insert.") If you need to sort, ungroup
the sheets, click the small rectangle at the intersection of the column
letters and row numbers to select the entire sheet, then perform the sort. Do
the same for the next sheet and so on.

If you need to protect data from accidental tampering, you'll find options
at ToolsProtection.

Best Wishes,

IanRoy


"FRUSTRATED" wrote:

Ok, let me explain. I'm on a military base here and I am making (trying to
atleast) a WORKBOOK that can keep track of who is on LEAVE and who is NOT ON
LEAVE. It is a very simple spread sheet to keep track of who should be here
or on vacation... as follows:

WORKSHEET 1: Is called "MASTER" and it would contain the list of everyone on
base as such...
Column A: (Last, First, Middle RANK) Their name and title
Columns B thru AF: (31 columns) indicating the amount of days in a month
(excluding February-28, April-30, etc...)
Basically, in those 31 columns (corresponding to the rows next to each
persons names) I would put an "X" for everyday that they would be on LEAVE
(vacation) and an empty slot would indicate that they should be on base
somewhere,... working (supposedly).

WORKSHEETS 2-13: (JAN thru DEC) would have the SAME info as the MASTER
worksheet except it would all be "future" data of when that person/people may
be on vacation or on duty that day. Make sense so far???

Theoretically if this all worked I could look at a month (worksheet) and
date (column) for the corresponding name (row) and know if that person is on
base or on Leave (vacation). TADA! =)



PROBLEM 1:
I created the MASTER worksheet to link to the other 12 worksheets (Janurary
thru December) so that when a new person arrives on base, I can enter their
name in the Master worksheet and "Sort A-Z" it would (should) "automatically"
update all the "Column A's" and corresponding rows for each name in all the
other worksheets (Janurary thru December). Unfortunately after I use the
"Sort A-Z" function it doesn't move the corresponding rows associated to that
name with the sort function.

Question for Problem 1:
How do I lock the MASTER Worksheet "rows" to their corresponding names so
that if I were to enter a new name and filter (Sort A-Z) wouldn't JUST sort
the names but also the corresponding rows to their names.



Problem 2:
I know how to link worksheets together... (my MASTER worksheet to all the
other 12 months worksheets) but I'm not sure if I'm doing it right. I'm
copying from my MASTER and using the "Paste Special - Paste Link" onto the
other 12 worksheets for Jan-Dec.

Question for Problem 2:
Is there a better method? When I use the Paste Link method it shows a bunch
of "0's" in the empty boxes which is producing a lot of clutter and
confusion. Is there a way to link the worksheets together to the Master
worksheet without having all those "0's"? <this workbook/worksheet must be
soldier/sailor proof...if you know what i mean


I hope someone out there knows what I am talking about...and knows the
answers to my frustrating situation...


Truely Greatful,

"Frustrated"

PS. None of the discussion links had the answers I was looking for.





--
"I should have paid attention in computer class..."



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to select rows in the repeat rows on top option Noppie Excel Discussion (Misc queries) 2 December 28th 04 03:17 PM
The $ thing to lock cells at rows or columns [email protected] Excel Discussion (Misc queries) 2 December 27th 04 11:57 AM
How do link to a remote worksheet using the path value in a field? Michael T. Links and Linking in Excel 3 December 11th 04 08:45 AM
How do link to a remote field but use the path from a stored field Michael T. Excel Discussion (Misc queries) 1 December 10th 04 12:18 AM
data entry on multiple worksheets diosdias Excel Discussion (Misc queries) 1 December 7th 04 05:33 PM


All times are GMT +1. The time now is 10:24 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017