for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

101 watchers

um Apr 2023 9:08am, 4 Apr 2023 7,245 posts	0 um How about one 'big' square cell for the date, with 4 (or whatever) narrow height cells underneath for the activity, eg something like
GlennR Apr 2023 9:40am, 4 Apr 2023 69,371 posts	0 GlennR Somewhat on um’s lines above, why even bother to concatenate? You could have all the cells on an output sheet adjacent to each other with whatever format you choose. Add borders to make it look like the report you want.
3M Apr 2023 9:49am, 4 Apr 2023 20,969 posts	0 3M Thanks all. The issue with concatenation comes about because the number of events in the report is variable, week on week, and the desire is to for the end result to not contain "blank" lines/cells. What started as a "sounds like it should be possible" is turning into a bit of a mare, simply because they want coloured words! I will see if coloured backgrounds work for them, and then possibly the formatting will work by excluding empty cells using "ISBLANK", somehow!
run free Apr 2023 11:10am, 4 Apr 2023 5,280 posts	0 run free Um - just comes my head. No time to look stuff up 😀
Diogenes Apr 2023 1:43pm, 4 Apr 2023 77,875 posts	0 Diogenes This might be a stupid question based on my basic level of knowledge (because if this was an option you'd be doing it already), but can't you just use conditional formatting to get the colours? Or is that complicated because you are pulling the data from another sheet?
JCB Apr 2023 4:07pm, 4 Apr 2023 4,064 posts	0 JCB Does this work? One way to match formatting when concatenating in Excel is to use the & character. This character will preserve any formatting that is applied to the cell. For example, if a cell has bold text and you use the & character to concatenate it with another cell, the resulting cell will also have bold text.
3M Apr 2023 5:40pm, 4 Apr 2023 20,975 posts	0 3M You'd think that from the description, wouldn't you, Diogenes? Sadly that only works if you are applying a single format to a cell from another cell. As soon as you add a second (differently formatted) cell, it seems to zap it and you end up with unformatted text. I did try! JCB, I've done that bit - based on the condition (content) of a cell to the right categorising the actual entry, formatted to colour and font. However, as soon as you concatenate or textjoin two or more together, the formatting simply vanishes. But..... I have manged to get it to do something like 80% of what I want it to do - manually selecting the right range and copying and pasting the coloured "list" of activities and dates sorted by date into Word or PowerPoint gets me 50% of the report. The high level calendar view can be created in a nice table in Excel (rolling dates provided by starting date + number of days), with the relevant daily items in cell below each date created by using =CHAR(149) & TEXTJOIN(CHAR(10)&CHAR(149),TRUE,{manually selected range of cells}). The "True" in the middle suppresses any empty lines in the block should someone change their mind and just blat it out of the list, and the CHAR(149) gives them a bullet, followed by a new-line delimiter of CHAR(10) and another bullet. That can also be copied into the Word/PowerPoint doc, and then any formatting re-applied manually, for the other 50% of what they want. But easier in a something designed to produce printed output than in a spreadsheet! It's a bit clunky, but better than what they do at present.
JCB Apr 2023 3:25pm, 5 Apr 2023 4,066 posts	0 JCB Does this work? =if(isblank(c1), “”, char(149) & c1 & char(10)) & if(isblank(d1), “”, char(149) & d1 & char(10)) etc…
JK chameleon May 2023 4:33pm, 16 May 2023 17,680 posts	0 JK chameleon Fetch Hive mind, please help a Jamie. Every month, I have a nasty spreadsheet that I need to extract lovely data from. Whilst I have a pivot table working for some of it, I need to find an easier way to do something else. Case in point, I have a worksheet with all the raw data, eg Name, Case Reference, Team, and then a large number of feedback columns. Is there a way of easily extracting the data, so that on different worksheets I can get a subset of that data (ie on the Bolton page, I'd like to list all the Bolton cases, but only the Case Reference, Name, and a couple of data columns). And then I'd like the same for the North Manchester cases on a different worksheet, etc. Reason for spinning the data into a different sheet rather than just filtering is I need to create graphs and suchlike on these other sheets, that I can then put into my report to my bossman. (Does this make any sense to anyone but me?)
rhb May 2023 4:37pm, 16 May 2023 2,058 posts	0 rhb xlookup should allow you to do this fairly easily?