Forum Discussion
Solved
How can I extrapolate a table into a list
Hi
I've spend a fruitless day trying to do what I know is a simple task. And utterly failed. I've a schedule of workshops over 4 days taking place in up to 6 different rooms. When we work out which workshop is going where it can be dumped into a timetable. So far so glorious. Now I just want to be able to squirt out the workshop numbers for each day, in day/time order, with the room number next to it. What could be simpler. Damned if I know. Would it be easier to use Word to get this list?
Unfortunately, the "Allow Edit Ranges" feature in Excel does not provide a way to give users full editing access to protected sheets while still restricting access to others. When you protect a sheet in Excel, it limits the users' editing capabilities to only what you have allowed through the "Protect Sheet" settings, regardless of any "Allow Edit Ranges" that have been defined.
As a result, users who are allowed to edit specific ranges using "Allow Edit Ranges" will still be restricted by the protection settings applied to the sheet, and they won't have full access to all editing capabilities, such as adding/deleting rows or using certain functionalities like CTRL+A -> FILTERS -> CLEAR ALL.
To achieve the specific level of access you described (i.e., giving eligible users full editing access to their respective ranges while still protecting the sheet for others), you may need to consider alternative solutions, such as using Excel VBA (macros) or a combination of data validation and worksheet events.
For example, you could use VBA to handle specific user access and manipulate the protection settings based on the users' actions. This would require a more complex setup and knowledge of VBA programming.
Please note that any solution involving VBA may require additional security considerations, especially if the workbook will be shared among multiple users in a shared network environment or on the internet.
If you are comfortable with VBA programming and require such a custom solution, you can explore VBA code options to handle sheet protection based on user actions. Otherwise, you may need to reconsider the level of protection and access needed for your workbook, balancing between data security and user convenience. Since no one has answered it for at least one day or more, I entered your question in various AI’s. The text and the steps are the result of various AI's put together.
My answers are voluntary and without guarantee!
Hope this will help you.
4 Replies
This option requires 365 insider beta at present.
= LET( wshp, TOCOL(ScheduleTbl), room, TOCOL(IF(ScheduleTbl<>"", rooms)), day, TOCOL(IF(ScheduleTbl<>"", days)), time, TOCOL(IF(ScheduleTbl<>"", times)), list, HSTACK(wshp,room,day,time), SORT(FILTER(list, wshp<>""),3) )- Hi Peter - That LOOKS pretty neat. I'm not sure I have 365 insider beta, so I've gone with Quadruple_Pawn's solution. But thank you. Such a brilliant community.
Sub wotkshops() Dim i As Integer Dim j As Integer Dim k As Integer Dim MaxRow As Integer Range("K:N").Clear MaxRow = Range("A" & Rows.Count).End(xlUp).Row k = 2 For i = 3 To MaxRow For j = 3 To 8 If Cells(i, j) <> "" Then Cells(k, 11).Value = Cells(i, 1).Value Cells(k, 12).Value = Cells(i, 2).Value Cells(k, 13).Value = Cells(2, j).Value Cells(k, 14).Value = Cells(i, j).Value k = k + 1 Else End If Next j Next i Range(Cells(2, 11), Cells(k, 11)).NumberFormat = "hh: mm" Range(Cells(2, 12), Cells(k, 12)).NumberFormat = "dddd dd" End SubYou can try this code. In the attached file you can click the button in cell J3 to start the macro.
- **bleep** I wish I could do that. Beautiful and super swift, thank you SO MUCH! I should have asked first thing rather than spewing out countless faulty sheets. That is fantastic.
