Copy cell from one sheet to another using checkboxes

[will_lower]

Hi all,

I am building a quoting tool within a price file, and as part of this, I have a sheet with the price file on it, and a sheet that will function as a shopping basket, with a blank quoting template.
I am trying to make this as easy as possible by implementing checkboxes (or equivalent), in order to allow the user to check items from the price table, and then press a button that will copy all the selected part codes in column A to the basket sheet.

If there is a better way to do this that I haven't considered, I would also like to hear it - otherwise, any help is much appreciated.

Thanks

 

[dreid1011]

You could do something like this:

Book1
	A	B	C
1	Part Code	Price
2	P123	10	a
3	P234	20	a
4	P345	25
5	P456	25
6	P567	20
7	P678	25	a
8	P789	10
9	P890	15
10	P900	20
11	P000	15	a
Price File

Book1
	A	B	C	D
1	Parts	Price	Qty	Total
2	P123	10	1	10
3	P234	20	5	100
4	P678	25	2	50
5	P000	15	1	15
6
7
8
9
10
11
12
13				175
Basket

Cell Formulas
Range	Formula
A2:B5	A2	=FILTER('Price File'!A2:B11,'Price File'!C2:C11="a","")
D2:D11	D2	=IF(C2>0,B2*C2,"")
D13	D13	=SUM(D2:D11)
Dynamic array formulas.

Not knowing how your sheets are setup, this is just a guess. You can use checkboxes, but they are very tedious to setup. I like to set the font of a column to Webdings, and type a lowercase 'a' to get a checkmark. Then, you can use the FILTER() function like above to include rows where col C = 'a'.

 

[will_lower]

You could do something like this:

Book1
	A	B	C
1	Part Code	Price
2	P123	10	a
3	P234	20	a
4	P345	25
5	P456	25
6	P567	20
7	P678	25	a
8	P789	10
9	P890	15
10	P900	20
11	P000	15	a
Price File

Book1
	A	B	C	D
1	Parts	Price	Qty	Total
2	P123	10	1	10
3	P234	20	5	100
4	P678	25	2	50
5	P000	15	1	15
6
7
8
9
10
11
12
13				175
Basket

Cell Formulas
Range	Formula
A2:B5	A2	=FILTER('Price File'!A2:B11,'Price File'!C2:C11="a","")
D2:D11	D2	=IF(C2>0,B2*C2,"")
D13	D13	=SUM(D2:D11)
Dynamic array formulas.

Not knowing how your sheets are setup, this is just a guess. You can use checkboxes, but they are very tedious to setup. I like to set the font of a column to Webdings, and type a lowercase 'a' to get a checkmark. Then, you can use the FILTER() function like above to include rows where col C = 'a'.

Thanks for your reply, I will give this a go - exactly what I was looking for, and much simpler than other solutions I had been attempting.
Much appreciated.

 

[dreid1011]

Thanks for your reply, I will give this a go - exactly what I was looking for, and much simpler than other solutions I had been attempting.
Much appreciated.

You're welcome, and happy to help.

 

[will_lower]

You could do something like this:

Book1
	A	B	C
1	Part Code	Price
2	P123	10	a
3	P234	20	a
4	P345	25
5	P456	25
6	P567	20
7	P678	25	a
8	P789	10
9	P890	15
10	P900	20
11	P000	15	a
Price File

Book1
	A	B	C	D
1	Parts	Price	Qty	Total
2	P123	10	1	10
3	P234	20	5	100
4	P678	25	2	50
5	P000	15	1	15
6
7
8
9
10
11
12
13				175
Basket

Cell Formulas
Range	Formula
A2:B5	A2	=FILTER('Price File'!A2:B11,'Price File'!C2:C11="a","")
D2:D11	D2	=IF(C2>0,B2*C2,"")
D13	D13	=SUM(D2:D11)
Dynamic array formulas.

Not knowing how your sheets are setup, this is just a guess. You can use checkboxes, but they are very tedious to setup. I like to set the font of a column to Webdings, and type a lowercase 'a' to get a checkmark. Then, you can use the FILTER() function like above to include rows where col C = 'a'.

I've implemented this a bit differently - instead of having a check column, the quantity column doubles as the check column, so any row with a quantity entered in the price file carries over to the basket, which then calculates the total cost - result of this can be seen below. Only issue is, this means that you cannot edit the quantity in the basket, which isn't ideal. Is there any solution to this, other than simply reverting back to using a check column instead of the quantity column?

Thanks - Will

 

[dreid1011]

Not without using VBA. An event code to trigger when you enter a quantity on the Price sheet would copy the data to the Basket, which you could then edit on the Basket sheet, but it wouldn't edit the qty entered on the Price sheet. You could use a second code on the Basket sheet to trigger if you do edit a qty to update the Price sheet. Let me know if you'd like to see this option.