3 Lawyers, JIM, ROSE and DAVE, make daily entries on their sheets 2, 3 and 4, respectively. Entries include: Case Name, Date Entered, Disposition etc.
How do I consolidate onto Sheet 1, the daily modifications of sheets 2, 3 and 4, BY CASE NAME (Alphabetically), and by Date Entered?
1 Like
Hi Ross, I built you an example of how you could do it. Have a look at my solution. In my solution you need a help table in a separate worksheet. The formulas in the sheet “consolidate” and in “help-sheet” have to be adapted to the amount of available data.
I have implemented the sorting with functions only for the first 5 characters for “Case Name”. Upper and lower case letters are not taken into account. I hope this is sufficient.
I hope this puts you on the right path!
rossalanblue.ods (18,3 KB)
The result is supposed to be an alphabetical Front Page list of clients by (CASE NAME) that updates whenever JIM, ROSE or DAVE change/update/ or add a new client. Here was my novice attempt attached here. This was set up for 50 clients for each Lawyer. The MAIN page sees their entries. I had to manually SORT ‘MAIN’ and copy to the ‘SORTED’ front page.
ELANE 3.ods (24.4 KB)
Hello Ross, I have now adapted my file to your specifications. I had originally understood the date differently.
The restriction with the first 5 characters when sorting is no longer there. I have now simplified the sort code considerably.
You can now decide whether capitalisation plays a role in sorting by switching on or off Case-sensitive in Tools - Options - LibreOffice Calc - Calculate - General Calculations.
rossalanblue.ods (55,5 KB)
1 Like
Am I too old? I cannot find fault with the sheet that you provided me. You are much more equipped with Formula composition, than I. You must have worked arduously, for me, and I thank you. I will be using your spreadsheet as a learning tool until I understand each command and TRY to see how it works. As it is:
- It appears, to me, that the help sheet shows I can add more entries on each of the 3 lawyers sheets for a total of 92. Is that right?
- It also looks like you ‘Conditioned’ those three sheets making 3 Styles.
- I haven’t tried to see how printing goes, and I see that the ‘Disposition’ column is on another page. Thanks again. Beautiful.
Now where is the 'Green Solved Check Mark?"
Hi Ross, you can make as many entries as you like in the lawyers. In the Help worksheet, cells B2:B4 are used to see how many entries there are. With the formula
=LOOKUP(2,1/(INDIRECT(A2&"!A1:A"&C2)<>""),ROW(INDIRECT(A2&"!A1:A"&C2)))
we get the last entry in the column. I used Indirect() to include the entries in A2 and C2. In A2 you find the sheet name and in cell C2 the cell range up to which the analysis is carried out. You can also enter a fixed value here. I thought this would make administration easier. So if you expect the data for the lawyers up to row 100, you can enter this in cell C2-4. If that is not enough later, then simply expand it.
You can also simply extend the data area in the Help worksheet. At the moment, the formulas are filled in up to line 92. If you need more, please go into the area and add rows.
Why not fill in the formulas at the bottom? By inserting in the middle of the formula area, whenever we address the whole range of cells in a column, you automatically expand it. The formulas in columns G and H in the help worksheet and all formulas in “consolidate” automatically expand to the new complete data range. After inserting rows, copy the formulas from the row above into the empty cells. This can be done in one go.
In the “consolidate” worksheet, I have added conditional formatting so that you can also use the colour code that you inserted when copying directly. Calc looks in column A and depending on the lawyer, a different format template is used. If you want to change this, please change the cell templates “Jim”, “Rose” and “Dave”. In cell H1 you can switch off the formatting. Please remember that you always need as many lines with formulas in “consolidate” as there are entries. To do this, simply copy from the last row with formulas to the bottom.
For printing, I would suggest deleting the cells in “consolidate” that are not needed at the moment. Set the columns A-F as the print area and set it to landscape format. Then it will work with all columns on one page. Like this:
rossalanblue.ods (58,3 KB)
If you have further questions, don’t hesitate to ask them here. Good luck!
You’re awesome. Do you think I can figure out how to add another lawyer?
I managed to change the names (5 places): the ‘Tab’, the ‘Help sheet’ 2 places, STYLE and Condition. I’m saving your answers.
I’m not awesome! I said that I could change the Lawyers’ names. I messed up the chart doing that. I’ve got a working one you sent for printing hints.
I’ve been studying your formulas, but have to read up on commands.
- I should be able to ‘Delete Contents’ in each Lawyer sheet. Right? And all else disappears from Help Sheet and Consolidation. Right?
- How would I change the Lawyer from Jim to John?
I’m totally burned out - 10 hours on this today. I think I need schooling.
e.g. what is “!A1:A” ? is it sheet1 all of Col A? Also, I don’t understand the arguments of INDIRECT with LOOKUP. I looked these up and their examples aren’t clear to me.
3) Is it difficult to add a 4th Lawyer?
Attached is what I’ve got. Seems to work.
LAWYER Sheet.ods (60.1 KB)
Hi Ross,
(1) you can add cells and delete rows as you like in the lawyer sheets. In the Helptable, I always start from cell A2 and add as many rows as there are. You should not leave any free rows between the data, otherwise they will also be displayed in the consolidation sheet.
(2) To change the lawyer names now, you only need to change the sheet name in the tab (by double-clicking on the tab) in the enclosed version. The formulas will now adjust to this in all places.
(schooling) To understand why I used indirect. Instead of writing in cell B2:
=LOOKUP(2,1/(INDIRECT(A2&"!A1:A"&C2)<>""),ROW(INDIRECT(A2&"!A1:A"&C2)))
one could also have directly:
=LOOKUP(2,1/(Jim.A1:A50<>""),ROW(Jim.A1:A50))
could have been written. Only that I have swapped out ‘Jim’ and ‘50’ into cells A2 and C2, so that I get the content from somewhere else. For this to work, you need the INDIRECT() function.
(3) I have now added three more lawyers as a precaution. Because it’s not that simple. If you don’t need any of the new ones, please don’t delete them. Hide the worksheet. It won’t bother you any more.
(question not asked) No, the formatting you use in the lawyer sheets cannot be transferred with this type of consolidation. If a text is written in a different colour, bold or italic, this will not be transferred to the consolidation sheet. No this cannot be done.
(extra tip) Before printing the consolidation sheet, you should definitely set “optimum height” for all lines used.
(Criticism) You have not continued working with the last worksheet. As a result, optimisations that had already been made were no longer available and had to be created again. If you destroy your sheet, you can always download it again from this website.
LAWYER Sheet_20221230_JK.ods (61,0 KB)
Happy new year
Jürgen
Thank you again, what do I owe you? You are very adept.
I could never have done this in 6 months.
I’ve saved your e-mails and originals, but must have uploaded, to you, one of my ‘working’ copies. Now, I’ve saved this one in 5 different places for backup. 3 on Desktop, on laptop and thumbdrive.
Q: (Are my messages to you being Posted? I hope not… too much needless info by me).
NOTE: I originally got the request from my daughter (Paralegal) who works for a Lawyer (boss) who manages subordinate Lawyers. I hope I can freely distribute this VERY NICE working copy to see if it suits her needs.
Hi Ross,
If your daughter then works with Excel instead of LibreOffice at work, some adjustments to the formulas will be necessary. The file will not work as it is.
In addition, depending on the version, it is much easier to do this in Excel.
That’s just a forewarning.
What happens if I export it in an EXCEL version?
I also asked her to get LibreOffice AND your original attachment that I sent to her… she said did not work. I assume she has EXCEL.
Elane, daughter’s, reply:
I have to use the programs the law firm has installed on my work computer. I cannot download other programs. EXCEL is what I’ll be using (Microsoft office 365)
Do I need to send you some money?
She just told me “Yes” she could use the EXCEL version.
Did I send to the right place? Did you get my e-mail : My daughter’s reply that “yes, she can use an EXCEL copy of your Lawyer sheet”
Hi Ross,
Consolidation can be done much more easily with Excel 365. Depending on the Excel 365 version your daughter is using, this can happen in a single formula. With Excel 365, not all new functions are rolled out to all users at the same time. However, the easiest way is with very new functions. Then the formula is:
=LET(raw,VSTACK(CHOOSECOLS(EXPAND(Tab_1,,6,'Help-Sheet'!A2),6,1,2,3,4,5), CHOOSECOLS(EXPAND(Tab_2,,6,'Help-Sheet'!A3),6,1,2,3,4,5), CHOOSECOLS(EXPAND(Tab_3,,6,'Help-Sheet'!A4),6,1,2,3,4,5), CHOOSECOLS(EXPAND(Tab_4,,6,'Help-Sheet'!A5),6,1,2,3,4,5), CHOOSECOLS(EXPAND(Tab_5,,6,'Help-Sheet'!A6),6,1,2,3,4,5), CHOOSECOLS(EXPAND(Tab_6,,6,'Help-Sheet'!A7),6,1,2,3,4,5)),nice, FILTER(raw,CHOOSECOLS(raw,2)<>""),SORTBY(nice,CHOOSECOLS(nice,2),1,CHOOSECOLS(nice,3),1))
The help sheet would only be needed for naming the worksheets. If I know beforehand what the tables are to be called and I am sure that no more will be added, I could leave it out.
Your daughter’s Excel version should therefore support the functions: LET(), VSTACK(), CHOOSECOLS(), EXPAND(), FILTER(), SORTBY() and TEXTSPLIT().
If these are not supported, I should know which ones worked.
Here is the sheet that will definitely only work in Excel. Doesn’t need to be tried in LO:
LAWYER Sheet_20221230_JK.xlsx (36,4 KB)
For your information, I have received all your posts. Please keep in mind that if you reply by email, it will still end up at the post in the original request at “ask.libreoffice.com”. So you are not replying to me personally.
If you would like to make a donation, LibreOffice The Document Fundation would be delighted to receive it.
LibreOffice is free software and is provided free of charge. Your donation, which is voluntary, supports our worldwide community. If you like the software, we would be happy to receive a donation.
best regards
Jürgen