Identifing variables between higher & lower values

Identifing variables between higher & lower values

Didn't find your answer?

Basically with out getting to complicated I have imported data from SAGE using the ODBC link for nominal monthly balances (NMB) and the Chart Of Accounts (COA).

I want a formulae that will look at the nominal code (N/C) from the NMB data and look up the relevant category in the COA by comparing the NC to the high and low values in the COA and give me the resulting category identiy.

This will help me in automating a process that gives my client a month to month Profit & Loss and Balance Sheet. Especially when the cleint introduces new NC's.

Is there anyone that can help? I have briefly reviewed the formulaes on Excel and have not found anything suitable. Though who does have all the time in the world to check all the formulaes and functions out!

I currently am using Excel Verions 2002 SP3.

Many thanks
Adam Hall

Replies (4)

Please login or register to join the discussion.

avatar
By adam.hall
17th Nov 2006 15:23

Thanks Paul
Thanks for the info it has enabled me to bring in the info direct form Sage and attach the relevant COA category automatically.

This will save me some time and gives me a great pro forma set up for getting some meaningfull KPI's without worrying if the client has setup new NC's.

Many thanks

Thanks (0)
avatar
By AnonymousUser
17th Nov 2006 14:21

Sage to Excel via MS Query
Adam -

This sounds very similar to something I have done for a client. In Excel (Office 2003) I have used Data / Import External Data / New Database Query / Sage. I have brought in a number of tables, which then need me to define matching conditions, which I do in the MS Query window. In my MS Query logic I use a condition that says "ACCOUNT_REF >=LOW and <=HIGH".

I'm not sure how different this is to your approach, but I hope it helps.

Paul


Thanks (0)
avatar
By adam.hall
17th Nov 2006 16:13

Duplicated entries for floating Sage nominals
Paul,

Your help has been great although now I have the floating NC in Sage (ie VAT, Bank etc) being duplicated.

Do you have any ideas about remmedying this through a microsoft query.

Cheers

Thanks (0)
avatar
By finegana
01st Dec 2006 13:20

MSQuery
I've used Query, but simply to dump data from various systems. My solution in the past has been to simply output the months results in a "Download" sheet...codes, values and anything else you want.
Sum the values in the Download sheet, to "check" against the COA sheet.
First time around, take the codes into this "COA" sheet by using advanced filter unique records only.
Take any analysis you've extracted into this sheet also, by using VLOOKUP.
Use SUMIF to drag the correct values through from the download sheet.
Sum the values brought through to this COA sheet, and your "check" is against the sum of download sheet.
Each month, refresh the query in download to extract relevant months data. If a new code is created, you will know this as the check with not balance; simply add those code/s to the COA sheet with the appropriate analysis.
Not sure I follow the HIGH and LOW thing though - I'm gussing it's just for analysis purposes.
I may have missed a step, cos I'm typing it without actually doing it right now!
Good luck

Thanks (0)