Strange Excel Issues

[zetaacosta]

Hi,

I have a couple of strange issues that I cannot get to the bottom of.

The first is that whenever the user opens the spreadsheet an error message is displayed advising that links cannot be updated. I have tried absolutely everything to get rid of this but to no avail. Any help on this would be appreciated.

The second is that my pivot tables are not picking up my dynamic range but this used to work (also if you type Data_Input in the range box in the top left, it picks the correct rows up). If the users adds some any details to the bottom of the data input page then refreshes the pivot it does not update, yet if you add the data before the last row then it adds them?

I have used Offset etc to calculate these ranges and as I say it used to work so I am unsure why it has gone wrong.

Sheet attached.

Thanks in advance.

 

[Hui]

I'm not seeing the missing links message in either Excel 2010 or 2013 ?

 

[zetaacosta]

Sorry... I sanitized the data to make it safe for the web and therefore removed a load of data. One of the cells I have deleted must therefore contain the reference.

I will go through the non sanitized data and find it.

Thanks.

 

[Hui]

The PT on the Projects gannt worksheet is linked to a fixed range: Data_Input!$A$3:$CJ$4
Not a Named Formula Data_Input

 

[zetaacosta]

Hi,

Thank you.

Is there a simple way to fix without breaking the whole sheet?

 

[Hui]

If the PT is meant to be linked to the Named Formula Data_Input
click in the PT
Goto Analyze
Change Data Source
Press F3 and select Data_Input

It updates to show a nice Gannt chart


Then Check other PT's on other worksheets as you need to change them all

 

[zetaacosta]

Awesome, thanks! That has done the trick.

 

[Luke M]

I did not get a message about updating links, so perhaps when you scrubbed the file for upload here, you managed to delete the offending formula/link?

For the PTs, the Data Source is not defined correctly. Looks like they're setup as
Data_Input!2:4

rather than:
Data_Input

If this was not accidentally done during setup, I would suggest using a Range Name different from the sheet name, in case there's some (?!?) odd XL bug.

 

[Hui]

or to be sure:
=Data_Input!Data_Input

 

[zetaacosta]

Thanks chaps, you're ace.