Creating a master worksheet

L

lrobin65

I have several worksheets that I would like to combine the data from onto one
worksheet. Is this possible and if so, how do I make it work?
 
G

Gilbert De Ceulaer

if you want the value of a1 of SHEET1 of WORKBOOK1 then put
='[WORKBOOK1.xls]SHEET1'!$A$1)

you could make this very easy to change (the names of the workbook, or the
name of the worksheet)
by putting "WORKBOOK1" in a1 of your destination-sheet
and "SHEET1" in a2 in your destination-sheet
then the formula would be =INDIRECT("'"&A1&"'!"&A2)

if you want an error-trap (in case A1 or A2 does contains wrong data)
the formula becomes =IF(ISERROR(INDIRECT("'"&A1&"'!"&A2)); "something is
wrong !"; INDIRECT("'"&A1&"'!"&A2))

does this help you ?
GDC
 
L

lrobin65

This works, but is there a quicker way to put the data onto a master sheet?
I have to change the formula at each cell and then do the update value after
each cell. I have over 15 worksheets in the workbook that go from A2 to V2
with sign up information.
Thanks for your help!

Gilbert De Ceulaer said:
if you want the value of a1 of SHEET1 of WORKBOOK1 then put
='[WORKBOOK1.xls]SHEET1'!$A$1)

you could make this very easy to change (the names of the workbook, or the
name of the worksheet)
by putting "WORKBOOK1" in a1 of your destination-sheet
and "SHEET1" in a2 in your destination-sheet
then the formula would be =INDIRECT("'"&A1&"'!"&A2)

if you want an error-trap (in case A1 or A2 does contains wrong data)
the formula becomes =IF(ISERROR(INDIRECT("'"&A1&"'!"&A2)); "something is
wrong !"; INDIRECT("'"&A1&"'!"&A2))

does this help you ?
GDC

lrobin65 said:
I have several worksheets that I would like to combine the data from onto
one
worksheet. Is this possible and if so, how do I make it work?
Click to expand...
Click to expand...
 
Top