Try this:

=INDIRECT("'ALL SHOPS'!"&Reference!$D$1&"2")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Danhalawi" wrote in message
...
My current worksheets look for information based on one cell...
However I want the worksheets to look at a cell that the user can change to
be the reference throughtout the workbook.

=All SHOPS'!$O$2
The above is my current formula repeated throughout the workbook with
different row numbers, what I want to do is set the formula to look at one
cell reference and based on what is in that cell the formulas changes the
column throughtout the book.

So ulitmatly I want the above to change column based on what letter is in my
cell.
The cell is:

=Reference'!$d$1 - cell reads - "g"

I hope this makes sense, and hope someone can help me with this.