Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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! :)

share|improve this question

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.

share|improve this answer
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. – Harold Mar 12 '10 at 17:24
@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 Mar 12 '10 at 18:14
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. – Harold Mar 12 '10 at 18:39
Just 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!!! :) – Harold Mar 15 '10 at 13:23

You must log in to answer this question.