SOLVED
Home

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
Concileus
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
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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies