Assign =concat( to a button?

[donnabee]

Hi there!
I'm essentially doing assessments in a workbook that has lines cut and pasted from a .csv
The csv is a list of photo names, coordinates etc.
I concat the photo names and then later all that information is fed into another workbook for upload - and the concat function tells that upload what photos to "take" from the folder.

The thing is, these sheets can be 10,000 lines plus so when identifying what photos I need, I end up typing =concat( hundreds of times.

I'd like to know if I can just have a button which would write the concat equation for me and I just go ahead and select the lines from there? So I select the cell where I'll be using the formula, press the button then hold control and click the cells containing the photos I need.

Please let me know if I haven't explained everything well enough.

 

[hajiali]

hello welcome to the board. Are you not able just use the Fill to transfer the formula all the way down. If not can you post examples of Cells, row, and column reference. It’s best if you post using XL2BB

 

[donnabee]

hello welcome to the board. Are you not able just use the Fill to transfer the formula all the way down. If not can you post examples of Cells, row, and column reference. It’s best if you post using XL2BB

Hi and thank you

I can't use fill for this column - if you see the image I have uploaded I'm using a traffic light system.
Green - start of damage
Yellow - damage continues
Red - damage stops

So I need to concat all the coloured rows, starting at the green one (I circled one for an example)

I probably should have uploaded the image to help explain, I apologise.

 

[hajiali]

Have you tried When you drag use the Fill without formatting. See image

 

[hajiali]

I should have mentioned you first need to use the custom sort to sort by filled color first before doing the above

See example of that Here

 

[donnabee]

Have you tried When you drag use the Fill without formatting. See image

That would concat all the photos, and I only need the photos on the coloured rows

 

[hajiali]

Did you try post #5 first the. Fill without formatting

 

[donnabee]

Have you tried When you drag use the Fill without formatting. See image

That would concat all the photos, and I only need the photos on the coloured rows

Did you try post #5 first the. Fill without formatting

It won't work, I need the colors to run as per the traffic light - Green then the yellow then the red, for their specific sections.

 

[hajiali]

I need the colors to run as per the traffic light - Green then the yellow then the red, for their specific sections.

Not sure what exactly you mean. What column do have that determines the
No fill
Green - start of damage
Yellow - damage continues
Red - damage stops


Perhaps we can do an If statement might be a better approach. In the future if value becomes one of the filled colors the formula automatically adjust

 

[Peter_SSs]

So I select the cell where I'll be using the formula, press the button then hold control and click the cells containing the photos I need.

See if you could use this. I have placed a Forms button in A1 (but it could be anywhere) and attached the following macro to it.

Sub ConcatNames()
  Dim rFormula As Range, rPics As Range
 
  Set rFormula = Intersect(Selection, Columns("B"))
  Set rPics = Intersect(Selection, Columns("A"))
  If rFormula Is Nothing Or rPics Is Nothing Then
    MsgBox "Error"
  Else
    rFormula.Formula = "=CONCAT(" & rPics.Address(0, 0) & ")"
  End If
End Sub

Now select all the cells in column A that you want concatenated and the cell in column B where you want the formula. It does not matter what order you do those things.
I selected all the green cells (cell B20 was empty when I selected it) and then pressed the button

donnabee.xlsm
	A	B
1	Filename
2	Pic1|
3	Pic2|
4	Pic3|
5	Pic4|
6	Pic5|
7	Pic6|
8	Pic7|
9	Pic8|
10	Pic9|
11	Pic10|
12	Pic11|
13	Pic12|
14	Pic13|
15	Pic14|
16	Pic15|
17	Pic16|
18	Pic17|
19	Pic18|
20	Pic19|	Pic2|Pic4|Pic5|Pic9|Pic11|Pic12|Pic13|Pic14|Pic18|
21	Pic20|
Sheet1

Cell Formulas
Range	Formula
B20	B20	=CONCAT(A3,A5:A6,A10,A12:A15,A19)