UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sharing List With Excel, Any Versions    
 
   
lex
post May 20 2019, 07:14 AM
Post#1



Posts: 749
Joined: 20-October 05



Hello, and thank you in advance for any thoughts. I work for state government and there is an issue with linking to a SharePoint list from Excel. I want to Get Data within Excel and receive error “Access to the resource is forbidden”.

It is always hard to get our IT to fix things.

I am therefore unable try what I am discussing here. My question is...

If I link to a SharePoint list from Excel and place it on an Excel sheet, then reference the data on another sheet, will any of the cell references change based on what occurs in the list? The question is kind of like how does Excel index to a SharePoint list?

Thank you for any thoughts.

Lex
Go to the top of the page
 
GroverParkGeorge
post May 20 2019, 08:02 AM
Post#2


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


What kinds of things could "occur in the list" that would change cell references?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post May 20 2019, 08:17 AM
Post#3


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


Although I'm not sure what process you're considering, it does look like inserting a row in a linked list does generate a cell reference error in a second sheet referencing that first one. Is that what you're looking at?

Attached File  CellReference.jpg ( 85.27K )Number of downloads: 1


Note that refreshing the data in the linked list on the first sheet also removes/reverses any changes made local in Excel.

What exactly would be the end goal here?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
lex
post May 20 2019, 09:17 AM
Post#4



Posts: 749
Joined: 20-October 05



Hi, George! As always, thanks for your help.

I'm promoting Governance - create once, use many times. If I can publish lists and have users "re-use" them, that would be great. Unfortunately, I'm pulling data from two systems (one querying oracle within Access, and the other pulling data into Access via Excel, joining the both and *hopefully* publishing to SharePoint). Again, I have a question to our IT department on why I'm getting the below error, and would like a reality check if what I'm hoping will work.

So...

QUOTE
What kinds of things could "occur in the list" that would change cell references?


I don't know, but I can't even test. So thank you for your test.

QUOTE
it does look like inserting a row in a linked list does generate a cell reference error in a second sheet referencing that first one. Is that what you're looking at?



I was *imagining* telling users to put a link to the list in one sheet, and within another sheet, reference the first sheet. I'm pretty sure you're inserting rows between the SP-linked rows. I just don't know what happens to the cell references if things change in the SP list. In my process described above, I have an external key (JobNumber) that I'll push to SP. I believe once I create a record in SP, I have to update any JobNumber records so that things don't change in Excel (vs delete/append updated records). But I don't even know how the Excel piece would work.

Again, thank you very much for any thoughts.

Lex

PS cloudy and rain in Michigan! I hope you're having sunshine!

smile.gif
Go to the top of the page
 
GroverParkGeorge
post May 20 2019, 09:35 AM
Post#5


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


I would expect that a change in the source list on SharePoint would be reflected in the local Excel sheet, and a second sheet reference it would also be updated.

It's going to rain all day here.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
lex
post May 20 2019, 10:42 AM
Post#6



Posts: 749
Joined: 20-October 05



I'm thinking that given SP Lists are indexed, if I append to the list once, and always update the record (and NEVER delete the record), and, in addition, if I add columns on the far-right of the list (as viewed) that the cell references won't break. Would you agree?

Lex
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 06:27 PM