Update Values Problem

[TomRet]

Hi and thanks for taking the time to look into this. I have a spreadsheet that's about 1000 years old that I have modified many, many times. As it is the beginning of the year I needed to modify again and I got everything looking pretty good (by my limited standards) until I started trying to find/replace things in the Year Total tab in order to change each row to copy data from it's respective tab (I.E. Employee 2, Employee 3.....)

Now I understand that the update values thing refers to external links, but there shouldn't be any external links. Is there a (quick...easy...) way to clean this workbook up so that there are no phantom external links or will I need to start from scratch? Any help would be greatly appreciated.

Thanks
Tom

[Glenn Kennedy]

I coouldn't see any external links. Can you be specific about what happens when you do whatever...

[TomRet]

In the Year Total tab anytime I try to modify the formula ='Employee '!D$2 or any variation of the same beginning with ='Employee ' it brings up the 'Update Values' window wi the option to 'open' some other excel document and no matter what I do it returns #REF!.

Thanks
Tom

[Glenn Kennedy]

Not on my PC, it doesn't... Do you see this problem on the file that is actually posted on the Forum?

[Glenn Kennedy]

Ah... If you try to change the sheet name to one that does not currently exist, then you will get that box opening. If you simply change the cell references, nothing happens. So if you want to change the target sheet and not get the pop-up, create the new sheet name first.

[Zer0Cool]

See if this works any better for you.

Noticed

='Employee '!D$2

had a space at the end of the sheet name. I took it out and the formulas on the Year Total sheet adjusted to match (maybe yours had not). Additionally I removed all the excess cells/formats from your file and the file size went from ~1MB down to 273KB, about 75% smaller file size.

[TomRet]

Actually that does seem to work. I noticed the space (frankly wasn't sure why it was there) but when I tried to delete it I got the same problem. Makes sense now that the sheet was not named properly. Thank you very much. What do you mean by excess cells/formats?

Thanks
Tom

[Glenn Kennedy]

Take a read at thsi (Excel file bloat)

https://www.techrepublic.com/blog/mi...orkbook-bloat/

[Zer0Cool]

What do you mean by excess cells/formats?

You had alot of white fill color beyond your data, so much so that Excel thought you were using like 75k rows instead of much less. If you have your old version compare it to what I sent side by side and you will see. Scrolling down a sheet on what I sent stops at the bottom of your data. Scrolling down yours went way past your data to like 75k rows.

[TomRet]

Awesome, I see it now. Thanks to both of you.

Thanks
Tom