how to vary cell name in formula

[Guest]

I've got a two-sheet workbook on Excel 2003. One sheet will hold various
information about employees and their paid benefits--one employee per row,
each column a benefits category. The other sheet is a calculator of how all
those benefits add up so employees can see their full compensation package.
I finally figured out the calculations based off one row of the first sheet,
but my question is how (short of copy-pasting and re-entering row numbers in
each formula) I can get that second page to vary according to which employee
(row) I want to print and present.
In other words:
if one of my formulae reads:
=(Census!$F8*52*Census!$E8+Census!$G8*52*1.5*Census!$E8-C14)
(Census is the first sheet's name)
and 8 is the row number for a specific employee.
How can I make that "8" variable so I can choose which row from the census
sheet is currently represented in the Calculator sheet?
Would there also be a way that with one action I could print that data sheet
with all possible employee iterations (one page per employee/row)?

 

[Guest]

I really don't understand your example here. I could easily help you out if I
knew what you were talking about. Why do you need to change the Row number?
And how is that row number determined? Is it just one cell with the formula?

Let me know if you have a better description. Because if you want to row to
vary you are going to have to use different formulas then you are using now.

 

[Guest]

You might want to look at the MATCH formula to see if it gets you where you
want to go.

 

[Ragdyer]

Try this, where you enter the row number in A1:

=52*(INDEX(Census!F:F,A1)*INDEX(Census!E:E,A1))+52*1.5*(INDEX(Census!G:G,A1)*INDEX(Census!E:E,A1))-C14


I don't quite follow what you want in the second part of your question.

 

[Guest]

Thank you for helping. I guess I wasn't very clear...sorry!
The formula I put as an example is a calculation for gross average annual
wages for hours worked. I refer to the formula sheet as "Calculator."
Census! is the sheet where all employee information is entered, and in
Census!, Column F is the average number of regular hours worked per week.
Column E is the hourly wage rate for that person. Column G is the average
number of overtime hours worked per week. (I used row 8 in the example so I
could check the calculations based on one particular set of data) On the
Calculator sheet, C14 is a calculation of personal days, in monetary terms.
The 52s in the calculation are for the weeks in the year and the 1.5 is for
time and a half for the overtime hours.
This is not the only formula in the Calculator sheet that uses information
from Census!, but I think if I could get this particular formula right, all
the others would be easier to get right. I'm pretty new to Excel, and REALLY
new to formulae.
What I've really been looking to do is this:
If in the original formula, there were some variable to replace every
instance of row #8, and I could easily inject whichever row number I wanted
(without having to change EVERY SINGLE INSTANCE on the page every time I want
to make the calculations for a different person/row), that would be ideal.
Does that make better sense?

 

[Guest]

THANK YOU! You're fabulous!
The gist of my second question is this:
if on the Census! sheet, each new row is a different employee--say I have 50
employees, each with their own information filled in. I know that if I fill
in an employee's row number in A1, it will populate the Calculator sheet with
the proper info, and I can print that single employee's calculator sheet--a
sort of statement of all employee benefits in monetary terms. Now, other
than changing the value in A1 for each and every employee/row, and printing
each calculation sheet individually, is there a way to select the whole range
of employee/rows from Census! to automatically populate and print in the
calculator sheet?
In other words, if I have employee information filled in in the Census!
sheet from rows 8-58, is there any way to bypass changing the value of A1
manually for every value between 8 and 58 when I want to print the
calculations for all 50 employees at once?

 

[Ragdyer]

What's confusing me about your explanation, is that you're referring to a
*statement of all employee benefits in monetary terms*.

My formula returns a *single* value.
Is this what you refer to as "Employee Benefits"? ... A single number?
OR
Are there additional columns (fields) of information that you want included
in this benefits *statement*.

If you intend to revise the suggested formula yourself, to include these
additional fields, and are just looking for a formula that is able to be
dragged down to copy, and automatically increment the row number, so that
you can return the entire list, you can replace the A1 with:

Rows($1:1)

This will *start* at Row1 and increment as its copied down.

To *start* at Row8, use:
Rows($1:8)

*ALSO* - you would probably need to make C14 absolute, so it *doesn't*
change as it's copied down.

=52*(INDEX(Census!F:F,ROWS($1:8))*INDEX(Census!E:E,ROWS($1:8)))+52*1.5*(INDEX(Census!G:G,ROWS($1:8))*INDEX(Census!E:E,ROWS($1:8)))-$C$14