I am trying to concatenate lots (100's of rows) of information together and will need to select the rows via a vlookup.
I will need to do lots of these conatenates and each one will have a different starting and ending cell.
I am trying to enter the cell address in concatenate function with 'Address' and 'vlookup'
'=concatenate((ADDRESS((VLOOKUP(D3,A:B,1)),2)):(ADDRESS((VLOOKUP(E3,A:B,1)),2)))
in short help!.
Many thanks.
d9h
Concatenate doesn't work with ranges.
I would have a read of the following post
It contains a UDF which will assist you
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Hi Hui,
Many thanks for the UDF 'concat"
it does really work very well.
however is it possible to put in a referance from another cell into the range so that the concat range is selected from a set of vairable values.
sorry to be a pain.
thanks
D9H
D9H
I would use a formula like this
=Concat(INDIRECT(C2))
where C2 has a Text Range ie: A10:A102
you can use a series of equations to make your range
eg: =Concatenate("A",C20,":A",C21)
where C20 would contain 10 and C21 would contain 102
Hui,
excellent Many thanks.
trully a massive time saver.
It works really welll thanks
the only issue i have now is that the concat is over flowing the ammount of data that can be put into the cell, but thats okay as it flags that something else is wrong in the data.... but thats not my problem...
Thanks again
You must log in to post.
Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.
Subscribe
