how to create a checkbox in a cell if the cell next to it contains text?

Question

I have used a filter formula and I want to make a column that shows checkboxes for every row that contains text from the filter formula result

I used this formula =FILTER(Table_1;ISNUM(SEARCH(C9;Table_1[Name]));"No result found") to create a searchbar with partial match showing up when searched in the search bar.

Now the next step I want to obtain would be to be able to select a list of some of the results that I have obtained with my search in order to use the info of that line of the table in other formulas.

To do so my idea was to create a checkbox that would appear next (and possibly linked) to each result I obtain from my search to be able to mark that search result as the right output I was expecting form my search.

I did try to use some macros but first of all I don't know how to code the instruction to create checkboxes on other cells and secondly i get some errors from using an iterative IsEmpty funtion in the macro

Answer 1

Seems like what you actually want is to preprocess and mark your data. Apparently there is no algorithm of choosing "good rows", you do so by reading the filtered output and using your judgement which rows to use and which to drop. The easiest way to do it will be adding a switch column directly to original data.

1. Let's assume your date is called myTable. Add to myTable a new column. Title it whatever you like: [isGood], [Flagged], [Selected] or anything that matches your purpose.

2. Filter your list in place for text strings you want (Excel is quite good at it).

3. Mark some of the filtered rows that you want to select for further usage by adding to empty [isGood] column a flag. If you need just one flag, a boolean value like TRUE or 1 is fine. If you need many subsets of rows for many occasions, use numbers or descriptive text flags like Alfa, Bravo etc.

4. Filter the list using any of

=FILTER(myTable[Name],myTable[isGood]=TRUE)

=FILTER(myTable[Name],myTable[isGood]='Alfa')

Answer 2

Assume that this is table with the name "Table3" on the active sheet.

H1	H2	H3
krumop	allma	fret
	dinny	blat
dlcsd	toc	tot
khorte	lanh	qert

The checked column for text is H1. This code insert checkbox in the column right to the table where H1 is not empty.

Sub chkbox()

Dim chk As Shape
Dim sh As Worksheet
Set sh = ActiveSheet
For Each cell In ListObjects("table3").ListColumns("H1").DataBodyRange

  If Not IsEmpty(cell) Then
   Set chk = sh.Shapes.AddFormControl(xlCheckBox, cell.Offset(0, 3).Left + 10, cell.Offset(0, 3).Top, 10, 10)
   chk.ControlFormat.LinkedCell = cell.Offset(0, 4).Address
  End If
  Next cell

End Sub

To get or set the linked cell of the checkbox use the ControlFormat.LinkedCell property. This code place the linked cell to the right of the checkbox.