Q:

Named Cells and Formulas In Excel

How do I utilize named cell references in Excel that aren't absolute.  I want to be able to take a formula and be able to drag it across excel and have one name cell reference update to a different named cell as I move across. 

For example:  I want to keep RevenuePerStay going across the formulas row and have excel updated the cell reference to the number of people staying.  So 
400 should be RevenuePerStay * Stay400

600 should be `RevenuePerStay * Stay600`

I tried using mixed cell reference and relative cell references using the dollar sign but excel will not accept this.

A:

Assuming your stays are in row 5:
For this worksheet, click on cell C7 and go to create a new named range called Stays and for the formula write =C$5$ and exit the name manager.
Now change your formula in C7 to being RevenuePerStay*Stays and drag it across. This will get the right amount of stays you want each time.
In explicit answer to your question: no you would never get the name in the formula to change unless you put all scenarios in the formula using multiple if statements. 

