I have set up a Vlookup that drags info from one excel sheet to another.
Its a stockbook for the Margin scheme.
The main sheet holds the stock numbers, plus data such as purchase price, sales price, sales info (invoice referencing and any notes), profit, VAT (its the margin scheme) etc.
An extra sheet holds the data re the deposits
Basic formula on main data sheet
=VLOOKUP(A719,Deposit!A:E,5,FALSE)
A719 = stock number line
This then reports the TOTAL deposits against that stock number where normally the sales invoice detail is held. (which will convert to sales invoice detail once the deal is complete!)
Now I have populated the column where the deposits will show in the main sheet, but as I have a ton of other info in that column, I now want to loose the ''#N/a ''when there is nothing to report from the Vlookup formula, so it looks less cluttered. I seem to recall having to add IFERROR but not sure how to do it, so any enlightenment would be useful (or I will have to trawl You tube).
Other thing then I want to add is if there IS a deposit I want to add a colour code to that cell. Again - I know this can be done through conditional formatting but wondering how to pick this up via the formula as the word 'deposit' will not appear - just an amount for them.
TIA
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
Using your original formula, you'd use
=IFERROR(VLOOKUP(A719,Deposit!A:E,5,FALSE),0)
to replace any error values with zero - often a good choice.
An observation I'd make is that VLOOKUP only ever returns a single value from the first matching row, if any, it encounters. Where there is one, or no, matching row this will represent the deposit TOTAL, but not if there's more than one deposit. You'd need to use something like SUMPRODUCT or SUMIF if there could ever be more than one deposit for any given stock number line..
If there'll only ever be a value other than zero in the cell if there is a deposit, you could conditional format based on when the cell value is not equal to zero.
Hi Ian
Was thinking that might work with the if error but there are usually reference numbers in that same column, but perhaps I should just introduce an extra column for 'total deposits' to cut out that issue. There are often several deposit/part payments, but I've added a list with a = sum to that other page to capture the total deposits, with the vlookup then just capturing the total.
I've never used sumproduct and didn't think about the if function.
Maybe I need to play around with it a wee bit more.
Thanks for your suggestions.
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
