Automatically checking a checkbox if criteria are met

Ian Smith 2017

We have a sheet to track our quotes we send out to customers and part of our quotation process is to credit check them, a successful credit check is recorded with a tick box in a column. Currently this is a manual process each time we send out a quote.

What I'm hoping to do is use a vlookup so I can look at the customer name and quote value on our quotations sheet and refer to another sheet (Credit checked) where I can record customer names and their credit limit. If the customer exists on the credit checked sheet and the quote value is below their credit limit then I'd like to automatically tick the checkbox on the quote sheet.

On my quote sheet I have the following columns: CUSTOMER(text number), VALUE (text/number), CREDIT CHECKED (tickbox).

On CREDIT CHECKED sheet I want to refer to I have the following columns: CUSTOMER(text/number), CREDIT LIMIT(text number).

I'm at a total loss as to how I achieve this😴

Ian Smith 2017

That's fantastic. Thank you so much!

Nick Korna

Hi @Ian Smith 2017 ,

This is absolutely doable!

The formula you would want in your quote sheet is:

=IF(Value@row < VLOOKUP(Customer@row, {Credit Check Range 1}, 2), 1, 0)

To do the cross sheet reference (the bit in the { } brackets) when typing the formula you would use this:

Find your credit checked sheet and highlight the 2 columns:

Once you've finished the formula you should get something like the following (with some examples filled in):

If you want to make things more visible you can use conditional formatting, or filters.

This will only check if orders are below their credit limit individually - I don't know if you would want to make something more complex with orders/invoices and whether the customer is below their total credit limit (i.e. if customer A has a limit of $100 and already has 3 invoices of $30 then a 4th order for $30 would not be approved).

Hopefully this helps though, if you've any questions etc. let me know!

Ian Smith 2017

That's fantastic. Thank you so much!