Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-24-2023, 03:46 PM
Taras49 Taras49 is offline Locking several cells and columns into one unit or block Windows 11 Locking several cells and columns into one unit or block Office 2021
Advanced Beginner
Locking several cells and columns into one unit or block
 
Join Date: Aug 2010
Posts: 33
Taras49 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 03-25-2023, 07:20 AM
p45cal's Avatar
p45cal p45cal is offline Locking several cells and columns into one unit or block Windows 10 Locking several cells and columns into one unit or block Office 2019
Expert
 
Join Date: Apr 2014
Posts: 866
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default

Quote:
Originally Posted by Taras49 View Post
and all subsequent groups of 6 rows and 4 columns
How many groups are we talking?
Is there any sort of naming structure to follow?
Reply With Quote
  #3  
Old 03-26-2023, 09:11 AM
Taras49 Taras49 is offline Locking several cells and columns into one unit or block Windows 11 Locking several cells and columns into one unit or block Office 2021
Advanced Beginner
Locking several cells and columns into one unit or block
 
Join Date: Aug 2010
Posts: 33
Taras49 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 03-26-2023, 09:37 AM
p45cal's Avatar
p45cal p45cal is offline Locking several cells and columns into one unit or block Windows 10 Locking several cells and columns into one unit or block Office 2019
Expert
 
Join Date: Apr 2014
Posts: 866
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default

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?
Reply With Quote
  #5  
Old 03-27-2023, 12:14 AM
ArviLaanemets ArviLaanemets is offline Locking several cells and columns into one unit or block Windows 8 Locking several cells and columns into one unit or block Office 2016
Expert
 
Join Date: May 2017
Posts: 873
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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!
Reply With Quote
  #6  
Old 03-27-2023, 02:05 PM
kilroyscarnival kilroyscarnival is offline Locking several cells and columns into one unit or block Windows 10 Locking several cells and columns into one unit or block Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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
Locking several cells and columns into one unit or block 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 several cells and columns into one unit or block locking word table cells while auto-updating fields fingermouse Word 6 01-14-2015 06:22 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:05 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft