|
Jan 2023
3:58pm, 21 Jan 2023
142,707 posts
|
GregP
As you were. This'll do:
=L2/COUNTIFS(D:D, ">=16/1/23", D:D, "<16/01/24")
|
|
Jan 2023
12:44pm, 22 Jan 2023
67,794 posts
|
GlennR
Makes sense.
|
|
Apr 2023
4:28pm, 3 Apr 2023
20,961 posts
|
3M
Thought I'd wake up the thread..... 
QQ please! Does anyone know how to preserve the formatting of individual cells when they are concatenated together in another cell Excel? Can it even be done?
I am trying to set something up to make events easy to read in a calendar layout for some "senior people" and the different categories of work are ideally colour coded. But to merge them into the results cell (so I can have a variable length/size output string & cell). I've actually used the "textjoin" function to grab the text values and join them with a chr(10) (carriage return). But there seems to be no way to preserve the original text formatting.
|
|
Apr 2023
4:37pm, 3 Apr 2023
2,499 posts
|
Bowman 🇸🇪
If there's no way with formulas, you could use VBA 3m
|
|
Apr 2023
4:41pm, 3 Apr 2023
20,963 posts
|
3M
Thanks Bowman - but I'm trying to set something up that eventual users can understand without needing to understand any type of "real" coding. I might just tell them to do "this" than apply the formatting manually before they send it!
|
|
Apr 2023
5:16pm, 3 Apr 2023
102 posts
|
CrispByNature 🇬🇧
If the formatting is consistent you could use TEXT to specify the formatting for each part inside TEXTJOIN?
|
|
Apr 2023
10:19pm, 3 Apr 2023
7,573 posts
|
Pothunter
Conditional formatting across the whole range?
|
|
Apr 2023
11:25pm, 3 Apr 2023
5,279 posts
|
run free
Mail merge into Word
|
|
Apr 2023
7:46am, 4 Apr 2023
7,242 posts
|
um
run free must have been reading the same web sites as me. I think moving to Word is one of the few options, other than manually re-formatting. Assuming it's colours or background you're trying to fix?
Is it easier in a word table that you build?
Or make each calendar entry a block of cells?
|
|
Apr 2023
8:53am, 4 Apr 2023
20,967 posts
|
3M
Yes.... I think an export to Word might have to be the answer!
Trouble is they need to enter 3 pieces of info - event, date, and "classification".
They then want it to build up to display the info in 2 different ways - one a simple "forthcoming week" list, and the other as a full month "calendar" view (i.e. a series of 7 x columns headed Sun-Sat, with corresponding dates in each cell beneath). They want one to be based on the other, but the text to be colour coded according to the category!
There's some fairly simple stuff in Excel around sorting, vlookups for grouping into categories or by date etc., and then copying/concatenating the output onto a pre-formatted report on another sheet, but fixing the colour coding has me stumped!
|
