Concatenate cells that change daily automatically?

1

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!

Maybe this will help to explain what I need. 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... All other parts of the formula will stay the same. I hope this helps! Thanks! :)

Harold

Posted 2010-03-12T16:51:59.063

Reputation:

Answers

0

First do you mean to say you have a column of data you insert daily? If so, which is what it looks like based on your description, this probably means you're data layout isn't ideal. You most often want your data to be in the long format. Database operations (and the corresponding Excel wannabe functions) work better when you've got your data set up so you can make easy queries. So you may want to fix that. That is unless you mean to say that The next day would be E5, E6 etc.

Now, onto your question. You can use $ to lock cells in formulas.

=CONCATENATE(TEXT('Raw Data'!$B$4,"m/d")," ",TEXT('Raw Data'!$C$4,"")," ",TEXT('Raw Data'!E4,"0.0%"))

You could place this formula in the a row/column that corresponds to your daily changing one and then drag it as the days change. Often a formula such as this would be in the last/first column and be drug/copied down the length of the data.

Tyler

Posted 2010-03-12T16:51:59.063

Reputation: 4 203

Thank you for your response! E5 would change to F5, F5 would change to G5, etc, based on the new day... I was hoping there was a way to automate this so to eliminate the need for manual intervention. – None – 2010-03-12T17:24:43.650

@Harold, where you have E5 you'd use E$5. You could also bust out some VBA. But at the point your using VBA to automate things in Excel I generally suggest that perhaps your spreadsheet is not ideal or Excel is the wrong tool. – Tyler – 2010-03-12T18:14:00.427

Yeah, I thought I would run into a problem attempting to use built in formulas vs. attempting to build VBA code. I have this same question out on stackoverflow for VBA help to my question. – None – 2010-03-12T18:39:28.310

1Just thought I would share the outcome to my question.

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

Thanks to all who tried to help me!!! Best regards!!! :) – None – 2010-03-15T13:23:16.637

Asked: 2010-03-12T16:51:59.063

Viewed: 1 711 times

Active: 2011-05-19T08:56:10.320