Worksheets

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Hey,

I've created a new spreadsheet that hold some information
about staff holiday leave.
I have a sort of front sheet worksheet with all the
members of staff, and then an individual worksheet for
each person, where their leave remaining is stored..
These worksheets are named FirstnameSurnameRef, they are
created by a macro when a new person is added.
How do I create a formula on the front sheet that
references their leave remaining. I've tried stuff like
=fnameSameRef!A20 (where fname,sname and ref refer to
cells where you have the name etc, and A20 the cell) but
it doesn't work.

Can anyone help?

Thanks
 
If I'm understanding you, the formulas below should work.

Let's say you have an employee named John, and the
worksheet sheet containing his data is named John. You
have his name in cell A1 of that sheet, and his remaining
leave total in cell B1. You want his data to display in
Cells A1 (name) and B1 (remaining leave) of your front
sheet. In cell A1 of the front sheet, enter =John!A1,
and in cell B1 of the front sheet, enter ==John!B1.

If you have the first name and last name in different
cells (say A1 & B1, and the remaining leave in C1) on
John's data sheet, you'll need to concatenate those cells
containing the name. In cell A1 of your front sheet,
enter =John!A1 & " " & John!B1. The "&" character "adds"
text strings together (you can also use Excel's
Concatenate function, but the "&" is easier). That's a
space character between the quotes, to insert a space
between the first and last names in cell A1 of your front
sheet. Then, in cell B1 of your front sheet, enter the
reference to C1 on John's data sheet (=John!C1)
 
Back
Top