external data imported help needed

[HASA]

I use Sage Line 50 for my accounts – but report to my board using excel. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
So within my spreadsheet I have two hidden sheets that I have set up using Import External data – using new database query – this imports my data from two account databases. My sheets in the spreadsheet now pick up the latest data from the imported data. (my predecessor used to manually input this data into all the sheets) – I have now got my ACCOUNTS DATA IN THE SPREADSHEET. <o></o>
<o> </o>
Now the problem I have is if a new nominal code has been entered in my accounts data (external data) then I refresh the data in Excel it then puts all my links out in my reports.<o></o>
<o> </o>
For example – say one of my sheets cell AM27 – HAS THE FORMULA =”SAGE Data L30” – with Sage Data being the external data - SO IT PICKS UP THE CELL L30 – however when I refresh the data another line is inserted as we have created a new accounts nominal – so the data I want in AM27 is now in Sage Data L31 – I cannot use $L$30 or $L30 AS REALLY MY DATA IS NOW SITTING IN L31<o></o>
<o> </o>
Does this make sense? <o></o>

 

[Andrew Poulsom]

Welcome to the Board.

You could use a VLOOKUP formula to get the data into your report. There needs to be a mapping between account codes and your output.

 

[HASA]

Thanks for the idea - it probably would work - but would need a lot of work to get this functional. Will give it a try and see - thanks again. H