Here's an implementation with expl3 and xparse:
\begin{filecontents*}{\jobname.bib}
@article{cite:a,
author={A. Uthor},
title={Title a},
journal={J. A},
year={2014},
}
@article{cite:b,
author={B. Athor},
title={Title b},
journal={J. B},
year={2014},
}
@article{cite:c,
author={C. Ethor},
title={Title c},
journal={J. C},
year={2014},
}
\end{filecontents*}
\documentclass{article}
\usepackage{xparse,letltxmacro}
\LetLtxMacro{\latexcite}{\cite}
\ExplSyntaxOn
\RenewDocumentCommand{\cite}{om}
{
\IfNoValueTF{#1}
{ \daniel_cite_or_ref:n { #2 } }
{ \latexcite[#1]{#2} }
}
\NewDocumentCommand{\addsubstitution}{mm}
{
\tl_gput_right:Nn \g_daniel_changes_tl { {#1}{\ref{#2}} }
}
\tl_new:N \g_daniel_changes_tl
\cs_new_protected:Npn \daniel_cite_or_ref:n #1
{
\str_case:nVTF { #1 } \g_daniel_changes_tl
{
% \nocite{#1} %%%% <---- uncomment this line if you want \nocite
}
{
\latexcite{#1}
}
}
\cs_generate_variant:Nn \str_case:nnTF { nV }
\ExplSyntaxOff
\addsubstitution{cite:a}{ref:a}
\addsubstitution{cite:b}{ref:b}
\begin{document}
\section{Something}\label{sec:some}
Here we do the citations: \cite{cite:a}, \cite{cite:b},
\cite{cite:c} and \cite[p.~42]{cite:c}
\appendix
\section{First appendix}\label{ref:a}
Text
\section{Second appendix}\label{ref:b}
Text
\bibliographystyle{plain}
\bibliography{\jobname}
\end{document}
You're supposed to use just one item in a \cite command; taking into account multiple citations with substitutions is possible, but it requires a decision about the final format.
With the \addsubstitution command you add the substitutions you want to do: the first argument is the citation key, the second argument is the label.
In the references only the actually cited items are inserted; if you want to add also the items that you substitute, it's sufficient to uncomment \nocite in the macros above.

Note on the implementation
The expl3 function \str_case:nnTF takes four arguments:
- the string to check;
- a set of pairs
{<string>}{<action>};
- what to do if a match is found;
- what to do if no match is found.
With the variant \str_case:nVTF, the second argument is a token list variable that contains the set of pairs.
The new \cite command will use the original \cite if the optional argument is given; otherwise it passes control to \daniel_cite_or_ref:n that checks the argument against the set of pairs stored in \g_daniel_changes_tl; this variable is populated by any number of \addsubstitution commands.
It would be easy, albeit not straightforward, to add other features, for instance accepting multiple keys in the argument of \cite and deciding what to do when a key needs the substitution.
\renewcommand\cite[1]{\ref{appendix:#1}}? – Henri Menke Sep 09 '14 at 15:52\cite, which is not what is asked for in the question. – DanielSank Sep 09 '14 at 17:11