Excel 2007 Cell References

[pslr2301]

I am not sure I am posting this in the right place. I can move it if necessary.

I am making a chore list for the kids. I have 2 sheets, one for every other week. This allows me to give one child a chore one week and another the same chore the next week. Week A, which is weeks 1 and 3 of the month, is the sheet where I fill out whose chore it is in the form of an initial. Week B uses a function to convert the information from Week A to the second child. I.E. Week A, D3, may have a "C". Week B corresponding cell has the function

=IF((WeekA!D3="L"),"C","")&IF((WeekA!D3="C"),"L","")&IF((WeekA!D3="L / M"),"C / M","")&IF((WeekA!D3="C / M"),"L / M","")&IF((WeekA!D3="M"),"M","")&IF((WeekA!D3="L / C"),"L / C","")

(This is probably not the most efficient way to do this but it works)

What I would like to do is instead of placing the function in each corresponding cell in WeekB, which automatically changes the function based on the cell it is in, to just reference the "function" cell in a separate sheet.
WeekB, cell D3, =Function!$D$3.
WeekB, cell J9, =Function!$D$3.

However, I would need the function part "WeekA!D3" to change according to the cell it is in. Example, "WeekA!CurrentCell" This is probably very simple and staring me in the face but I am just not seeing it. Not sure if this makes much sense but I can explain further if needed.

Thanks for any help!

[Bob Phillips]

What is the rationale here? Are you worried that if you have to change the formula, you will have to change many? If so, fear not, you can select them all, change the formula, and hit Ctrl-Enter to do them all in one.

[pslr2301]

What is the rationale here? Are you worried that if you have to change the formula, you will have to change many? If so, fear not, you can select them all, change the formula, and hit Ctrl-Enter to do them all in one.

Thank you for your quick response. My main intention for asking was to find out if it could be done. I am always trying to learn new tricks to simplify the things I do. Your idea is something I will definitely try in the future! =)