concatenate

5 Topics

Avoiding #NAME? Error/ Calculating date difference
I am creating a share point list that tracks our plan progress through the year. I have 2 date columns - [planned start date] and [actual start date]. I have the current formula in place: =IF(ISBLANK([Activity Start Date]),"0",DATEDIF([Planned Start Date],[Activity Start Date],"YM")) However, When the[actual start] is BEFORE the planned date, it gives me the #NUM? error rather than a negative value. I have found previously the suggestion to use a formula: =IF(ISERROR(DATEDIF(TODAY(),[Due date],"d")),"0",DATEDIF(TODAY(),[Due date],"d")) I adapted it to fit my list: =IF(ISBLANK([Activity Start Date]),"0",IF(ISERROR(DATEDIF([Planned Start Date],[Activity Start Date],"YM")),"0",DATEDIF(([Planned Start Date],[Activity Start Date],"YM")) However, received a syntax error.
Solved
Abi_Mcdaniel
Nov 22, 2022 Place SharePoint
2KViews
0likes
2Comments
Structured Reference - Concatenate
Hi All, first post. Attempting for some compliance controls to utilize excel tables with structured reference formulas. The table with structured references will help protect the formulas appropriately for our compliance concerns. the first issue is i need to concatenate two values in this format: A2&"_"&B2. In the structured reference world, i am coming up with =CONCATENATE(Table1[@[A]:[B]]), but this is giving a #Value error. Can anyone confirm if concatenate will work with a structured reference? or if there is another workaround?
JoeCavasin
Nov 03, 2021 Place Excel
4.2KViews
0likes
4Comments
New Spill function making concatenate output difficult?
I'm no Excel whiz, but I need to be able to concatenate the contents of cells in 9 columns into one, for each row in a 1000 row spreadsheet, with comma-delimited output. I used to be able to do this quite easily up until the most recent Excel update. Now, the concatenate function uses Excel's new "SPILL" function to paste the result into adjoining empty cells next to the one I am placing my formula in. Isn't the reason for Concatenate to join the selected cells text into one cell? Anyway - I have not found a way that I can bring the output back into just one cell. I need to be able to copy these outputs into another spreadsheet, and while highlighting a cell in the spill output shows the blue outline for the range, copying the first cell only copies the first cells contents. Can anyone help?
Solved
nickcicc
Jan 27, 2020 Place Excel
30KViews
1like
6Comments
vlookup using concatenate function and helper column
Hi - I've set up a table to record project costs. To save time, there are some standard unit costs that I want to pull through from another tab in the same worksheet. For example Consultant 'Joe Bloggs' has a day rate of £500. So if the selections from the drop down lists in two of the columns on the costing sheet match 'Joe Bloggs' and 'Consulting Fees' I want the unit cost to automatically populate as £500. I found a solution that described using the CONCATENATE function to merge two values into a 'helper' column in my look up table. It partially worked, but not all the values were coming through correctly. I've tinkered with it and now none of the values are coming through at all!! Can anyone spot where I may have gone wrong on the attached file?? (NB: I did try this using IF and AND functions which does work, but the number of variables became too large) Thank you
Solved
Angela McGhin
Feb 13, 2019 Place Excel
3.2KViews
0likes
4Comments
How auto concatenate many cells?
Hi. I have 1000 text cells in Excel. I need to concatenate it into one cell. I try to use =CONCATENATE(B1: B999), but it not works. When I try to use some as =CONCATENATE(B1; B999) - it's work, but, of course, I see a text from b1+b999, and nothing else. Help me, please, how I can to auto-combine text from all 1000 cells? Thnx a lot!
Андрийчук Виктор
Jan 27, 2018 Place Excel
1.7KViews
0likes
4Comments