One way

Put the reference range in A1: F2:G17 (say)

List the names of the sheets across in B1, C1, D1 ... etc
e.g. in B1: Daniel, in C1: Mark , tec

Then you could put this formula in B2:

=VLOOKUP($A2,INDIRECT("'"&B$1&"'!"&$A$1),2,FALSE)

and simply copy B2 across

B2 will return the same as:
=VLOOKUP($A2,Daniel!F2:G17,2,FALSE)

and C2 the same as:
=VLOOKUP($A2,Mark!F2:G17,2,FALSE)

and so on
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"NICK" wrote in message
...
Hello

I have a lot of sheets in an excel workbook and a front summary sheet
where
i have a vlookup formula that i would like to copy across to the right
however each column needs to change the formula to pick up the data in the
different sheets. Does anyone know a macro that will help me so that each
column copied across looks up in the next sheet.

Eg. 5 sheets - Daniel, Mark, Byran, Hugh & Paul
on the summary page i need it to go =VLOOKUP(ref,Daniel!F2:F17,col,false)
then =VLOOKUP(ref,Mark!F2:F17,col,false) then
=VLOOKUP(ref,Byran!F2:F17,col,false) then
=VLOOKUP(ref,Hugh!F2:F17,col,false)
and then =VLOOKUP(ref,Paul!F2:F17,col,false).

Because I have 50 sheets doing a find and replace is quite time consuming

Cheers
Nick