I need help to automate my concatenate to change daily without having to manually input the new data entered each day.

Question

Hi there

I use concatenate to pull data together from different cells in my spreadsheet. Since my data changes daily, I want the formula to also change daily without having to manually input the new cell in the concatenate formula. I am looking for a way to do this but not sure how. Can anyone out there help me out please!? I appreciate the assistance in advance!

UPDATE: Is there a way to use VBA coding to automate the concatenate process based on the following criteria? I have a row of data from D4:AH4 that I insert daily based on the new day. When I use the concatenate and us the following formula: =CONCATENATE(TEXT('Raw Data'!B4,"m/d")," ",TEXT('Raw Data'!C4,"")," ",TEXT('Raw Data'!E4,"0.0%"))... E4 being the cell that changes daily where next day would be F4, G4, etc... B4 = Today() C4 = Text of my choice E4 = is current days data but changes to the next cell daily. Example E4, F4, G4, etc...

Answer 1

It seems you need to create a dynamic range using a combination of OFFSET and COUNT. See here, or Google for "excel dynamic range".

Answer 2

=TEXT(B4,"m/d")&" "&C4&" "&TEXT(OFFSET(D4,0,COUNTA(D4:AH4)-1,1,1),"0.0%")

I use the & operator instead of CONCATENATE, but they do the same thing. The last element uses OFFSET. It starts at D4 and moves 0 rows down and x columns to the right, where x is the COUNTA of what's in D4:AH4.