refresh excel data weekly question

  • Thread starter Thread starter Geoff Brandt
  • Start date Start date
G

Geoff Brandt

I have a multi-user spreadsheet that needs to get refreshed with new and
updated excel data on a regular basis. My team keep notes and updates some
fields/columns. My desire is to bring in the wholly new rows and the updated
existing rows and not overwrite the fields/columns that my team is writing
into. There is a column of unique data elements that would be considered a
key field in a dbms.

I could do this in 30 seconds with a database but I'm having trouble
figuring it out in Excel (which I have to use). From what I've been reading
it appears I have to set up the incoming new data as an ODBC source????

Any help would be appreciated. MS Excel 2002 sp-2

-gb
 
It's not clear how the new data is arriving now, but you could keep the
data that's being replaced on one sheet. On a separate sheet, have
columns with the key field, and the columns that people are updating
manually.
 
I wish I could do that but the target sheet with the notes is full of
formulas, protected fields, arrays etc. I only need the weekly new data to
populate columns in the target sheet that either don't have formulas or are
not areas that my team will edit. My problem is I don't know how to
automatically import/copy an excel sheet into another that will only
populate some columns and not others. I don't see a way around imported
empty cells copying over cells with data...

-gb
 
You still haven't explained how you're currently importing the data.
If the data is downloaded to an Excel file, you could use MS Query
(Data>Import External Data>New Database Query) to connect to the
download file, and intersperse the imported columns with columns that
contain lookups, or other formulas.
 
Yes, the weekly new data is simply another excel spreadsheet I'm given from
another business unit as an email attachment.

I'll look into MS Query.

thanks,
-gb
 
You could detach the weekly update, and save it with the same name as
the previous update

Then the query, when refreshed, would automatically pick up the new data
 
Back
Top