The Bookkeepers.Network (BKN)

Post Info TOPIC: #N/a


Master Book-keeper

Status: Offline
Posts: 6666
Date:
#N/a


Hi BKNers

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 McCormick

Fallows Hall Ltd

Winner - Bookkeeper of the Year 2015, 2016 and 2017

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.



Senior Member

Status: Offline
Posts: 428
Date:

Sorry Joanne, I would love to be able to help but this is way above the little spreadsheets that I play around with.

Hopefully someone will be on soon to help you out



__________________

Doug

These are only my opinions of how I see things and therefore should not be taken as advice



Senior Member

Status: Offline
Posts: 184
Date:

Hi Joanne,

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.

Regards,








__________________

Ian

Ian Brown FCA
Onion Reporting Software Ltd

www.onionrs.co.uk

Sage accounts in Excel. No set-up necessary. Free trial.



Master Book-keeper

Status: Offline
Posts: 6666
Date:

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 McCormick

Fallows Hall Ltd

Winner - Bookkeeper of the Year 2015, 2016 and 2017

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.

Tweet this page Post to Digg Post to Del.icio.us
Members Login
Username 
 
Password 
    Remember Me  

2018 The Bookkeepers Network (BKN). All Rights Reserved. The Bookkeepers Network (BKN) is a trading division of Bookcert Ltd. Registered in England Company Number 05782923. 2 Laurel House, 1 Station Rd, Worle, Weston-super-Mare, North Somerset, BS22 6AR, United Kingdom. The Bookkeepers Network and BKN are trademarks of Bookcert Ltd. This forum is a discussion forum only. There will usually be more than one opinion to any question and any posting should not be viewed as a definitive solution. No responsibility for loss occasioned to any person acting or refraining from action as a result of any posting on this site is accepted by the contributors or The Bookkeepers Network. In all cases, appropriate professional advice should be sought before making a decision. We reserve the right to remove any postings which are offensive, libellous, self-promoting or engaged in covert marketing. We will not notify users of removals. The views expressed in the forum posts are those of the individual and do not necessary reflect or agree with those of The Bookkeepers Network. Any offensive or unsuitable posts will be removed by the moderators. Any reader of this forum can request for a post to be looked into by sending an email to: info@bookkeepers.network or info@bookcert.co.uk.