refresh static columns in excel along with the dynamic columns

Question

I am working on two spread sheets say A and B, the sheet A has data that is directly being pulled from the database using a sql query in the excel connection. It has real time vendor data and keeps changing as and when we refresh the sheet.

I have another sheet B, which is pulling the data from sheet A using the vlookup formulas, in addition to the vendor data it has another static column named "notes". We add some text into the notes column for each vendor.

My problem is, whenever sheet A refreshes, the data in sheet B changes automatically but static notes column doesn't change, as the data changes the notes column is being mapped to the incorrect vendors. Is there a way we can sync the dynamically changing columns and static columns ? I want my notes column to be correctly mapped to the vendors.

I am using excel 2010 version, thanks for your help!

Cheers,

Answer 1

You can make an extra sheet with only the names of the vendors (by using something like =IF(COUNTIF(Sheet1!A$1:Sheet1!A1,Sheet1!A1)=1,Sheet1!A1,"") I've used sheet1 as sheet with the dynamic data. This way you select every vendor only once. Now you can write notes in cell B.

Then in your static data sheet use something like =VLOOKUP(A1,Sheet2!A$1:B18,2,FALSE) so that you find your notes by the corresponding vendor. (Sheet2 is my list of vendors with notes, cell A is the name of the vendor, B are the notes).

Does this answer your question?