#1
|
|||
|
|||
Locking several cells and columns into one unit or block
Let's say I wanted to name a block of cells a1 to d6. I want to then name that group and all subsequent groups of 6 rows and 4 columns. It seems that that should be an easy task but I can't figure it out. TIA
|
#2
|
||||
|
||||
How many groups are we talking?
Is there any sort of naming structure to follow? |
#3
|
|||
|
|||
It is a spreadsheet similar to an address book but with more information. It is a club dues ledger with data such as name, address, date joined, lockers, quarterly payments etc. Each member's "block" contains 6 rows by 20 columns. I would like to sort the entire spreadsheet using different criteria at different times, such as sort by name or sort by dues paid date, etc. I'm sure it is possible but have no idea how. Thanks for any help you can provide.
|
#4
|
||||
|
||||
Sorting blocks of multiple rows is very hard work. There are better solutions. Chiefly, it involves separating your raw data from the data you want to see (your various 'reports'). The raw data remains the same (except when you want to change it) but you present it elsewhere in the ways you want to see.
You managed to respond without answering either of my questions! However, I have some more: What data does each column (20 of them) contain? What version of Excel are you using? Strongly suggest you attach a workbook (adjusted to disguise sensitive data without losing realism). Are macros allowed? |
#5
|
|||
|
|||
I strongly advice to drop this design, as it only adds for you problems in future, and it also will be prone for data entry errors (i.e. entering some info into wrong cell).
In case all 120 fields of info per person are different unique type of information, the best solution for data entry sheet will be a single row for every person. When some of person info can be grouped (especially when the info for such group is usually entered in same time moment), and there is a field which always identifies the user for 100%, you may use a design, where the will be a separate information entry sheet for every such information group, along with column which identifies the person. But remember, this design will make handling your tables more dificcult! In case some info of same type is repeated in different fields (like different phone numbers, mail addresses, bank accounts, etc.), I strongly advice to enter such info into separate table for every such info type - a separate row for every such information unit. As the number of information fields is big, you probably need a report sheet, where you can select a person, and the full information about this person is displayed in any format comfortable for viewing/printing (as one possibility, the format which you currently use for data entry). In case you have for whatever reason several information entry sheets, having such report is a must! Remember, designing a workbook you have to consider: 1. How difficult is entering new data; 2. How difficult is making any calculations on stored data; 3. How comfortable are your data for viewing/printing. Usually, p.1 and especially p.2 arent compatieble with p.3 - and you can compensate this using report sheets! |
#6
|
|||
|
|||
One thought: Do a mail merge to Word.
It depends on how your data will be organized in Excel, but it seems to me that your blocks could Word Tables, and you could import from Excel after sorting your Excel data. The other thing you might want to consider in Word is having a separate worksheet in your workbook using the =FILTER function, if you have Word 365 or another version that supports the FILTER function. Your second worksheet could populate based on the original table but sorted differently, or filtered on where March fees paid is 0, and so forth. Check out this video for more on what this looks like: https://www.youtube.com/watch?v=Eehk6PC0oGs |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how to create a block text and when click on the title of block hide and unhide the block | labasritas@free.fr | Word | 4 | 09-17-2017 01:17 PM |
Locking cells | melvin1942 | Excel | 6 | 07-18-2017 11:42 PM |
format changes after locking cells | dlquinte | Excel | 2 | 04-21-2016 08:31 AM |
Locking a signature block | adrieller1 | Word | 1 | 06-08-2015 05:45 PM |
locking word table cells while auto-updating fields | fingermouse | Word | 6 | 01-14-2015 06:22 AM |