how do I use a cells from one worksheet in the equation of another

[mclwlg]

I also have a similar problem to this and I am a complete Excel novice.

My Excel 2007 version workbook currently contains 18 worksheets, 1 of which
is a Master Sheet, that contain mainly Date based staff Certification info
(eg: 25/02/2009,16/01/2009, 18/02/2008) and I am wanting copy the
Certification dates pertaining to each individual person's tasks that they
are certified for from the other 17 worksheets into the corresponding columns
& cells in the Master sheet and be auto updating as the data changes in the
other worksheets.
Unfortunately the 17 worksheets need to be kept as individual sheets.

To complicate things further, I need to maintain compatibility with Excel
2000 SP1, Excel 2003 as well as Excel 2007 versions as we have a mixture of
all 3 versions on the network.

Hopefully somebody can help me here?

 

[JLatham]

Jacob Skaria has provided what is probably the simplest, fastest operating
solution to referencing the data on the other 17 worksheets.

As for maintaining compatibility - if the workbook was created in an earlier
version of Excel than 2007, then it should show up as being opened in
Compatibility Mode and it should then save in the earlier format when you
save/close it. If it was created in Excel 2007 and is a 2007 format like
..xlsx or .xlsm then you can force it to save in Excel 97-2003 format by using
File | Save As and choosing that option from the available formats.

You can even set your Excel 2007 up to default to saving files as Excel
97-2003 format by clicking the Office Button, then clicking the [Excel
Options] button and choosing {Save} from the left-hand list in the dialog
that opens and choosing the Excel 97-2003 format from the drop-down list next
to the "Save Files in this Format" entry.

BTW: to set things up like Jacob recommended, you can get Excel to pretty
much do all the typing for you. Click a cell on the 1st sheet and type the =
symbol, then choose the sheet that has the information you need on it and
then select the proper cell on that sheet and press the [Enter] key and Excel
will build the proper formula for you and won't make any typo's in spelling
the sheet's name.

If the lists on the other 17 sheets change a lot, you might want to look
into using the VLOOKUP() function on the main sheet to return the information
from the other 17 sheets.