You can use INDIRECT

=INDIRECT("'Sheet "&COLUMNS($A$1:A1)&"'!K9")

copied across


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"RAF" wrote in message
...
I have a significant number of identical worksheets that may contain
different values I need to consolidate to a single worksheet. If I have a
column in the consolidation worksheet that pulls data from a source
worksheet
using cell formulae such as: ='Sheet 1'!$K9, is there a way in which I can
get this to copy into adjacent, assending columns such that the worksheet
reference would be automatically incremented i.e.: ='Sheet 2'!$K9, ='Sheet
3'!$K9, and so forth? I cannot seem to find anything that is on point but
I'm
sure there must be a way to accomplish this.