View Single Post
  #6   Report Post  
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..."