conditionally update cell references from 2nd worksheet

[JulioFuentes]

Hello. My Excel expert is away unexpectedly and my new boss needs something done right away (nice). I could really use some help! I hope this is clear.

Thank you!

Using Excel 2003. I would provide the 'real' workbook, but do not see how to attach a file in this posting. The problem is probably not that hard, but there is a lot of text here only to help make it clear.

The book has two sheets.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
In the Salaries and Benefits sheet Columns A and B refer to the other sheet named 'GEA Sal Sch'
EX: from Salaries and Benefits
A5 ='GEA Sal Sch'!$B$77 (displays the value 10.0)<o></o>
B5 ='GEA Sal Sch'!$E$77 (displays the value 68,887)<o></o>
<o> </o>
NOTE:<o></o>
1) Salaries and Benefits! Column A always references Column B in GEA Sal Sch!, but the Row referenced will differ.<o></o>
2) Each Row in Salaries and Benefits! Columns A and B always reference the Same Row in GEA Sal Sch!<o></o>
<o> </o>
***Here is the logic I need help incorporating:***<o></o>
<o> </o>
IF the value in Salaries and Benefits! column A = 10, then Salaries and Benefits! A and B remain unchanged - keep the same references<o></o>
EX: Since A5 = 10.0, then Salaries and Benefits! Row 5: A5 will remain ='GEA Sal Sch'!$B$77<o></o>
B5 ='GEA Sal Sch'!$E$77. No action taken.<o></o>
<o> </o>
IF the value in Salaries and Benefits! column A = 9.5, then Salaries and Benefits! A and B reference row increments by 1.<o></o>
EX: Since A6 = 9.5, then Salaries and Benefits! A6 must increment one ROW from ='GEA Sal Sch'!$B$76 TO ='GEA Sal Sch'!$B$77.<o></o>
AND Salaries and Benefits! B6 must increment one ROW from ='GEA Sal Sch'!$E$76 TO ='GEA Sal Sch'!$E$77.<o></o>
<o> </o>
ELSE<o></o>
Salaries and Benefits! A and B reference row increments by 2.<o></o>
EX: Since A7 = 6.0, then Salaries and Benefits! A7 must increment one ROW from ='GEA Sal Sch'!$B$69 TO ='GEA Sal Sch'!$B$71.<o></o>
AND Salaries and Benefits! B7 must increment one ROW from ='GEA Sal Sch'!$F$69 TO ='GEA Sal Sch'!$F$71.<o></o>
<o> </o>
<o>*** </o>
I have programming experince, but no VBA and not much Excel. I know how to write OFFSET and INDEX functions, but I do not know if they are useful for what I need to do, nor do I know if an Excel IF statement can use functions in place of the
value_if_true, value_if_false

I am wide open to suggestions, but I want to avoid making the end-user copy/paste any function, etc. unless it is very simple for them. I am hoping to automate the logic and cell updates as much as possible.<o></o>

 

[JulioFuentes]

UPDATE - simpler logic, but I still need some advice, please.

I can simplify all of this by changing the Salaries and Benefits! sheet to replace all the cell references in column A with the values – that should help. The OFFSET statements shown below work.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
***However, I do not know how to incorporate them while retaining the different cell references in column B.***

UPDATED LOGIC
Here is an updated, simpler IF THEN. I substitute the row numbers with # and give example cells to help. The goal is to do this:

IF
A# < = 9.0 then increment A# + 2, and increment the ROW reference in B# + 2
EX: A7 < = 9.0 is True then A7 = A7 + 2. B7 =OFFSET('GEA Sal Sch'!$B$69,2,0)

IF
A# = 9.5 then increment A# + 1, and increment the ROW reference in B# + 1
EX: A6 = 9.5 is True then A6 = A6 + 1. B6 =OFFSET('GEA Sal Sch'!$E$77,1,0)

Thank you very much for any advice you can offer!

 

[JulioFuentes]

THIS IS SOLVED. I found the Morefunc add-in for Excel and managed to get this working. Used FORMULATEXT() to extract the text of the formula(cell refereces) and then used OFFSET(INDIRECT()) in counjunction with FORMULATEXT to update the cell references.

http://download.cnet.com/Morefunc/3000-2077_4-10423159.html