A workaround is to specify the coordinate explicitly. See Extract x, y coordinate of an arbitrary point in TikZ if you still want to call it by node-name.

\documentclass{article}
\usepackage{tikz}\usetikzlibrary{spy}
\begin{document}
\begin{tikzpicture}[remember picture,spy using overlays={connect spies,blue}]
\node(a)[circle,fill=yellow]{Text};
\newdimen\tempx\newdimen\tempy
\pgfextractx\tempx{(a)}\pgfextracty\tempy{(a)}
\spy[rectangle,height=3cm,width=5cm,magnification=4]
on(\the\tempx,\the\tempy)in node at(4,0);
\end{tikzpicture}
\end{document}
Why did \spy fail with node-name?
Actually \spy is still working. For example

\begin{tikzpicture}[remember picture,spy using overlays={connect spies,blue}]
\node(a)[circle,fill=yellow]{Text};
\spy[rectangle,height=3cm,width=5cm,magnification=4]
on(a)in nodeat(1,0);
\end{tikzpicture}
The problem is that the magnificent glass is not focusing on the right position. Assume you want to spy on (a) in (b). Then you need to apply the transformation
shift(vector=(b))*scale(factor=4)*shift(vector=-(a)).
But somehow the follows is applied.
shift(vector=(b)-(a))*scale(factor=4)*shift(vector=-(a)).
I do not know why. So far the only thing I can tell is: go to tikzlibraryspy.code.tex and check out the definition of \tikz@lib@spy@do#1#2#3. Between the following two lines
\tikz@scan@one@point\tikz@lib@spy@shift#2%
\pgflowlevelsynccm%
there are two messenger-dimensions \pgf@pt@x and \pgf@pt@y bringing the wrong vector. This happens only when TikZ needs to parse a node-name.
Can I \spy on some remembered point?
I guess you, and probably some other users, are here because you want to remember a point and spy it later. However this cannot be done in this manner.
When one mark a scope (in this case, the whole tikzpicture) with spy using ..., TikZ stores this scope in a box called \tikz@lib@spybox. It prints this box once normally and once for each \spy. After this scope the memory is released.
Therefore, if you want to spy something not in the current scope, you need to copy that scope instead of remembering the position.
Can I \spy something outside? headers/footers?
No, unless (i) the whole page is now a tikzpicture; or (ii) you extract materials by hand.
As far as I know (ii) is possible. For instance crop package is used to do something on the whole page. It should contain something useful.