SOLVED

Updating Excel Table

%3CLINGO-SUB%20id%3D%22lingo-sub-786337%22%20slang%3D%22en-US%22%3EUpdating%20Excel%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786337%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%20table%20that%20is%20connected%20to%20a%20SharePoint%20database.%20I've%20added%202%20extra%20columns%20within%20the%20Excel%20file%20(not%20found%20in%20the%20SharePoint%20database)%20to%20keep%20track%20of%20changes%20pertaining%20to%20the%20status%20of%20each%20document.%20Each%20document%20is%20on%20it's%20own%20row.%20These%20other%20columns%20are%20%22Notes%22%20(track%20changes%20in%20status)%20and%20%22PDF%20File%20Path%22%20(hyperlink%20that%20brings%20up%20the%20document%20file%20created%20after%20running%20VBA%20Macro%20to%20create%20the%20PDF%20file%20from%20fields%20in%20the%20current%20row).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPROBLEM%3A%20Every%20time%20the%20Excel%20file%20updates%20from%20the%20database%2C%20the%20info%20I've%20added%20in%20those%202%20columns%20don't%20stay%20with%20their%20respected%20row%3B%20they%20shift%20to%20random%20cells%20within%20the%20same%20column%2C%20but%20to%20a%20different%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20keep%20all%20the%20information%20in%20one%20row%20to%20reflect%20each%20document.%20I've%20been%20searching%20for%20a%20solution%2C%20but%20I've%20been%20unable%20to%20solve%20this%20problem.%20Any%20help%20would%20be%20greatly%20appreciated.%20Thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUPDATE%3A%20My%20first%20line%20of%20data%20starts%20at%20cell%2038.%20After%20adding%20a%20note%2C%20creating%20a%20PDF%2C%20and%20hitting%20%22Refresh%20All%2C%22%20the%20note%20and%20PDF%20hyperlink%20move%20down%20to%20cell%202355%20(in%20the%20same%20column).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-786337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786543%22%20slang%3D%22en-US%22%3ERe%3A%20Updating%20Excel%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786543%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386565%22%20target%3D%22_blank%22%3E%40Concileus%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20not%20think%20your%20way%20of%20adding%20notes%20will%20work.%20Excel%20would%20not%20be%20able%20to%20remember%20specific%20database%20row%20you%20put%20your%20notes%20against%2C%20unless%20these%20get%20pushed%20back%20to%20the%20database.%20To%20keep%20your%20notes%20aligned%2C%20you%20would%20need%26nbsp%3Bto%20add%20them%20directly%20in%20the%20SharePoint%20database.%20Alternatively%2C%20you%20could%20add%20your%20notes%20in%20a%20separate%20table%20against%20a%20unique%20identifier%20from%20each%20row%20of%20the%20SharePoint%20database%2C%20then%20use%20INDEX%2FMATCH%20formula%20in%20the%20main%20SharePoint%20query%20table%20to%20reflect%20these%20notes%20against%20the%20respective%20rows.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps%3C%2FP%3E%3CP%3EYury%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello!

 

I have an Excel table that is connected to a SharePoint database. I've added 2 extra columns within the Excel file (not found in the SharePoint database) to keep track of changes pertaining to the status of each document. Each document is on it's own row. These other columns are "Notes" (track changes in status) and "PDF File Path" (hyperlink that brings up the document file created after running VBA Macro to create the PDF file from fields in the current row).

 

PROBLEM: Every time the Excel file updates from the database, the info I've added in those 2 columns don't stay with their respected row; they shift to random cells within the same column, but to a different row.

 

I'm trying to keep all the information in one row to reflect each document. I've been searching for a solution, but I've been unable to solve this problem. Any help would be greatly appreciated. Thank you!

 

UPDATE: My first line of data starts at cell 38. After adding a note, creating a PDF, and hitting "Refresh All," the note and PDF hyperlink move down to cell 2355 (in the same column).

1 Reply
Highlighted
Solution

Hi @Concileus,

 

I do not think your way of adding notes will work. Excel would not be able to remember specific database row you put your notes against, unless these get pushed back to the database. To keep your notes aligned, you would need to add them directly in the SharePoint database. Alternatively, you could add your notes in a separate table against a unique identifier from each row of the SharePoint database, then use INDEX/MATCH formula in the main SharePoint query table to reflect these notes against the respective rows. 

 

Hope this helps

Yury