Assuming Jon is in cell B2 you could use a formula like this...

=indirect("'" & B2 & "'!A1")

Indirect takes a text string that is a cell address and returns the value
from that cell. Note that this function is volatile which means that it has a
lot of overhead associated with it. If you have thousands of such formulas
you will probably see a substantial drop in performance for you spreadsheet.
--
HTH...

Jim Thomlinson


"scuba" wrote:

I'm trying to create a formula so when i copy it across a couple columns it
will refer to the column header which are also worksheet names. Then, as the
worksheet name in the column header changes the function will look at that
page. For example, let's say the column headers below also refer to
worksheet names and I want to return what is in cell A1 of each worksheet.

Jon Bob Steve

='Sheetname!'A1

What I'd like to do is just copy the formula across so it refers to Jon,
Bob, and Steve and returns A1. Does this make sense, and is it possible.