122 MAY2020|COMPUTER SHOPPER|ISSUE387
HELPFILE&BUSINESSHELP
I’ve recently become self-employed, and
needed away to keep track of my invoices.
Ilookedintoafewonline accounts services,
but theyall seemed more complex than I
needed. Ibuilt aspreadsheet in Google
Sheets tracking the number,dateand
customer foreach invoice,together with a
description of the work. Iadded fields for
the total I’d billed, the amount I’d been paid,
and asimple formulathat helps ensure I
save enough moneyfor my tax bill.
I’ve encountered my first overdue
invoice,and realised that it would be good
to add acolumn showing the due date,
along with some conditional formatting
to turn this red when an invoice is late.
Imanaged to do all this by trial and error,
but then Iran intoproblems. CurrentlyI
manually apply agreen highlight to an
entire rowwhen that invoice is settled, but
the conditional formattingoverrules it,
giving paid invoices one distracting red cell.
Irealised it would be good to use
conditional rules to automatically apply
the green highlight to arowwhen Imark
that invoice as settled, but I’ve run out of
ideas. My ‘tax’ column would be auseful
trigger,asitonly contains afigure once
the invoice is paid, but Ican’t work out
howto useone cell or range of cellsto
trigger conditional formatting on a
differentrange of cells. Canyou help?
Dan Neilson
Sheets does let you define adifferent source
and destination forconditional formatting.
From the Format menu, select Conditional
formatting and click Addanother rule,then
define the target range across which you want
the formatting to be applied. In your sheet,
something like A3:J1001 will include all the
active columns and give you plenty of rows.
Change the Format cells if...box to ‘Custom
formula is’ and enter=$H3<>"",then define
the green highlight and click Done.You’ll see
the entire spreadsheet, save the column
headings, light up green.
Forthe highlight to apply only to paid
invoices, you need to get rid of the hyphens
that appear in your Taxcolumn in the unpaid
and blank rows. This column is populated by
simply dividing the amount you’ve been paid
by three,sowhen the Paid field is empty
there’s an error,resulting in the hyphen.
Remove it by changing the formula forthe tax
cell on row 3from =(G3/3) to=IF (G3<>"",
(G3/3), ""),which either displays avalid sum
or creates ablank cell. Copythis cell and paste
it down the column to keep your existing
correct figures but lose the hyphens.
The green highlighting should now apply
only to paid invoices, but you’ll notice that
the red highlight you defined foryour Due
column still overwrites the green once paid
invoices go beyond their due date. To fix this,
re-open the Conditional formatting sidebar if
Conditionalpayment
necessary,then select the
entire Due column. Move
the cursor to the leftofthe
green highlight rule until it
turns intoafour-way arrow,
then drag it up above the
red rule and release it.
SCSI please
Iuse an ArtixScan 4000t
filmscanner.This connects
to my Windows 7PCvia a
SCSI card, which Device
Manager reports as an
Adaptec 2915/2930LP.I’m
considering updating the
computer to Windows 10,
but Idon’t think Adaptec
provides asuitable driver.
Do Ihavetokeepa
Windows 7machine just so I
can keep using my scanner?
Bill Edwards
Unfortunately,the only way
to be certain is to test the
card with Windows 10.
Although Adaptec has a
Windows 7driver forboththe 2915LP and
2930LP,neither card is listed on its
Windows 10 support page.This maywell
mean that the card will continue to work
using the Windows 7driver –that’s the
case forseveral other Adaptec products –
but we can’t be sure.
If it’s important to know before you
upgrade,see if afriend with aWindows 10
PC will let you temporarily install the card
and its Windows 7driver,but take abackup
first. Otherwise you could upgrade your own
machine to Windows 10 as atest. If the card
no longer works, you’ll need to choose
between investing in anew scanner or SCSI
card, or downgrading back to Windows 7.
Youcan do this within 10 days of upgrading
by opening Settings, selecting Update&
Security,then choosing Recovery in the
left-hand pane.
⬆You’llhave10daystorevert to Windows7if your scanner doesn’t
work under Windows 10
⬆Paid invoices are marked
green, overdue ones have the
due datehighlighted red
⬆The greenformatting is triggered by populated cells under Tax,
but the hyphen counts
