Importing Data From Sage

Hi

I am importing 2 columns of data (Part No. and Description) from our accounts program (SAGE LINE 50)

I am then doing a VLOOKUP on this data and adding a further columns of information. If someone adds a new Part No. to Sage then of course my VLOOKUP will not capture it as it doesn't know it exists.

How can I be made aware of a change in the data import table automatically on refresh? I thought about using a conditional format to compare the number of rows in the 2 tables and change the font colour. This wouldn't tell me what had been added, but it would warn me that the tables are out of sync.

Any other suggestions greatly appreciated.

To be clear (it's Monday, sorry), you're wanting a visual indicator when something new appears in your query/import when you refresh it? Or are you wanting to make sure that your vlookup/etc update every time a refresh retrieves new data?

Is there a reason you're doing a vlookup to the query results in order to add new columns of data, rather than just putting the columns of data right next to the query, so that upon refresh, the additional columns update/refresh automatically as well?

Hi

I am doing a several VLOOKUPS from the one data sheet and then adding the new info on these sheets. The main reason is say I have 50 items in the stock table that I import. This will be in columns A & B rows 2 to 51 allowing 1row for the header.

If I input my data in cells C2 to C51 and then someone adds a new product to Sage which is sorted alphabetically, when I next import the data from Sage it will now occupy cell A2 to B52 and my data in column C will no longer line up with the correct part number.

Unless there is a way it can be done where the data in column C is automatically always linked to a particular part number no matter where it is.

Ah, ok.

You can add your extra columns/formulas in blank columns immediately adjacent to the query columns, and set the table such that those extra columns will automatically update as well.

Right-click on your query results/table, and choose External Data Range Properties (in earlier versions), and at the bottom make sure that 'insert cells for new data, delete unused cells' is checked, and at the very bottom, make sure that 'fill down formulas in columns next to data' is checked. With these settings, if new parts are added (or removed, for that matter), when the query updates and returns more parts than were there before, Excel will automatically make sure that your extra columns are adjusted accordingly (will automatically fill down the formulas correctly, etc).

Hope that helps!

That sounds good. I will give that a try.

Thanks

MACKE

Hi

I have been on holiday so haven't had a chance to test it till now.

I guess what I do need is some sort of notification when an item is added/deleted from the data import from Sage.

MACKE