[Question] Microsoft Excel question

Dave

Dave

Staff member
Okay, all, this is a really weird one. Let me start off by saying I'm a Microsoft Excel Certified Super User...and this one has me completely stumped.

I have a huge Excel workbook with about 20 tabs that run pivot tables from a single data sheet. Today I was speaking with some users who need to have a sheet set up that feeds from one of these tables but can't be a pivot itself. So it's got to be a bunch of VLOOKUPs and references with error checking...you get the idea.

So I make a new tab, make all the rows I'm going to need, then I tried to make my first reference...and the font in the formula bar changed. It went from this
from this.png
to this to this.png . Actually, when I did this the first time the N79:N81 did not show and instead was nothing more than blank spaces. The screen print shows what happened during troubleshooting.

Note that I didn't change anything in the settings. I couldn't figure out how to get rid of it, so I did the following to troubleshoot.

  • Checked for macro. None.
  • Had someone else open it. Worked the way it was supposed to. So it's my PC.
  • Opened a different report on my PC. Worked fine. So it's the report itself.
  • I rebooted and the above screenshot is now what appears. So I can now at least see the cells referenced. That's a step forward.
  • On a whim I went to earlier iterations of this report and the issue presents itself all the way back to October 2, 2015. October 1, 2015 on back this does not happen.
I have tried Googling this, but the results I get only show how to change the font in the formula bar by messing with the File --> Options --> Advanced --> Display and changing the sheet display when you start Excel. But that's not what this is because it's only happening to the one sheet.

I'm stumped and so is my Excel Certified Super User boss who knows more than me. Thoughts?
 
Dave

Dave

Staff member
Nope. The workbook changed from 10/01/2015 to 10/02/2015. It's very strange.

What's really weird is that a little bit ago working on this it had THREE different font styles in the formula bar: normal text but bolded, the large purple text, and invisible text.

I got it to work anyway just by not looking at what I was doing and typing it all in. And amazingly enough, it worked fine the first time. Which is

=IFERROR(GETPIVOTDATA("Name",'Per User (Term)'!$A$5,"Sales User",'Satellite Prod'!$A2,"Stage",'Satellite Prod'!C$1),"")

It was a pain in the ass to get the correct referencing in.

This is what it looked like once I was done. Notice the different formatting.

Error #3.png
[DOUBLEPOST=1473451917,1473451805][/DOUBLEPOST]And by the way, when you do that thing that was in the link you provided, it affects ALL excel sheets, not just the one you are in. And since this only affects my one sheet, that's not the answer. I looked at that first. But damn do I wish you were right!
 
papachronos

papachronos

What about extracting the xml from the 10/1/15 and 10/2/15 iterations and do a side by side comparison? Maybe you can catch something in the formatting that the sheet content can't account for.


Sent from my iPhone using Tapatalk
 
Dave

Dave

Staff member
Nope. They are exactly the same. In fact, we take the previous day, overwrite with the new data, and refresh the pivots.
 
You must log in or register to reply here.
Top