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.