In the calendar view sheet - Cell E9 for example - my objective is to have each vegetable show "Quantity - Vegee - Location" on each line. But the CHAR(10) function is placing every piece of data on its' own line.
Is there a way to adjust the formula to keep associated data together and only add a line break for the different vegetables?
How about
=SUBSTITUTE(IF(VLOOKUP(E8,'Date Table'!$D$6:$AI$888,32,FALSE)=0," ",VLOOKUP(E8,'Date Table'!$D$6:$AI$888,32,FALSE))," ",CHAR(10),{3,6,9})
Confirm with Ctrl Shift Enter, rather than just Enter
Hmm... easiest way to handle it is to change your "Input sheet" O column formula.
=IF(C4<>"",E4&"-"&C4&"-"&F4&G4&H4,"")
Copy down. No other change needed.
Result will look like...
0.JPG
If you wanted to preserve single space rather than "-" in the Calendar view.
You can add additional substitute to your formula in Calendar view.
=SUBSTITUTE(SUBSTITUTE(IF(VLOOKUP(E8,'Date Table'!$D$6:$AI$888,32,FALSE)=0," ",VLOOKUP(E8,'Date Table'!$D$6:$AI$888,32,FALSE))," ",CHAR(10)),"-"," ")
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
@Fluff13, not sure that would work
@Pencil+Pen,
I think you have an issue on your Date Table formula with relative/fixed references etc and, given your version, you could simplify to:
Formula:
Please Login or Register to view this content.
remainder of initial post removed in favour of CK76s approach which is better -- though I'd say the double evaluation becomes superfluous once you apply the SUBSTITUTE:
Formula:Please Login or Register to view this content.
Last edited by XLent; 07-23-2020 at 11:17 AM.
It works in 365, but I can't test to see if it also works in 2013.@Fluff13, not sure that would work
Hi, doesn't work for me w/O365 .. that said, I am on the semi-annual channel (and rely on work for updates) so I still don't have likes of FILTER etc...
(but, given above, it won't work with earlier versions)
for reference I was testing the approach with =SUBSTITUTE("appleapple","p","^",{1,3}) but I only get "a^pleapple"
Thank you all for replying.
@CK76 - Your method worked. Thank you! I tried to add to your rep again but it won't let me!
You are welcome and no worriesBut you may want to consider giving reps to others that contributed.
FYI - With O365, there are more efficient method. Your AI column formula can be changed to TEXTJOIN(), which has argument to use custom delimiter and ignore blanks. That will significantly simplify downstream manipulation. But for backward compatibility, I tend to avoid these new functions, unless document is for internal use only.
I get the same result as you with that formula, although it worked on the OP's file.Originally Posted by XLent
Just another oddity, which is irrelevant here as it obviously wont work for the OP.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks