INDIRECT.EXT Not Updating SharePoint Closed Data

[Salut39]

Hi Guys,

I use INDIRECT.EXT which works fine for closed files on network but when I make a reference to a SharePoint saved file it only updates values if the file is open.

Any way to fix this as I have lots of files there?

Thanks
Yuri

 

[SkipVought]

hi,

Is this a Microsoft OFFICE question?

forum820

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Salut39]

Yes this is Excel linked file to other Excel documents saved online.

 

[GlennUK]

I'd say that it's a Sharepoint question ... are Excel documents able to be directly linked to Excel Sharepoint storage? I'd guess not.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Salut39]

I can manually link them, retyping the formula but I was thinking to automate this process.

 

[Salut39]

='

https://internalportal.emihub.com/New

Revenue Streams - Hub Team Site/Private Documents/J NEW RELEASE PROCESS/A. New Release Master List and Entry Form LIVE/[NR0000350.xlsm]MasterList'!F$12

This is sample of the link:

=INDIRECT.EXT("'"&A1&"["&A2&".xlsm]"&A3&"'!"&A4)

This is my formula which only works when the NR0000350.xlsm is open.

 

[Gruuuu]

Seems that function (introduced in Excel 2010) has some bugs.

I would suggest finding an alternate solution.

An easy one, I've found, is to build out the string as if it were going inside of the indirect function, but display it as a string in the cell. Then, copy the results and paste value in the intended location, and finally convert it to a formula.

Example:
in [B1], ="='"&A1&"["&A2&".xlsm]"&A3&"'!"&A4
in [B2], copy [B1], PV in [B2]
in [B2], find and replace "=" with "="

As you can see this is easily scriptable, and you can have your users (or yourself) change a few drop-downs and click a button.

 

[Salut39]

Thank you Gruuuu!

I was thinking of the easier way to do it for less advanced users but your variant is working too.

Yuri

 

[Gruuuu]

Some of my users experience experience unhealthy levels of anxiety when they see an excel function they don't know. So I started using this method because, well, I just don't always have the time to deal with them.