Nested IF with OFFSET?

Hello. My new boss threw me a curve yesterday. I need help with this, please! I have programming experience, but Excel is pretty new to me.

I have 2 sheets in the same workbook. Sheet1! has rows with two cells that contain references to Sheet2! like this:

Sheet1
U35 ='Sheet2'!$B$75
V35 ='Sheet2'!$E$75 (different column, same row referenced)
U36 ='Sheet2'!$B$67
V36 ='Sheet2'!$D$67 (different column, same row referenced)

You can see the two columns from each record line above reference the same row in Sheet2!, and this is consistent through all of Sheet1!

The array in Sheet2 is Attached and Highlighted: 'Sheet2'!$B$56:$G$77

***Here is the logic I need help incorporating:***

If U35 = 10, then U35 and V35 remain unchanged - keep the same references
If U35 = 9.5. then both U35 and V35 reference row increments by 1
Else both U35 and V35 reference row increments by 2

The test is against the referenced value in Sheet2! Column B - every time. The values in Column B are: 0, 0.5, 1.0, 1.5...,9.5, 10.0

I know how to write the OFFSET function OFFSET('Sheet2'!$B$75,2,0), but I do not know (sorry) where to place the formula nor do I know how to incorporate the two OFFSET functions in the nested IF statement. Should I use a Helper/Temporary/Hidden cell?

I know OFFSET is volatile, but it is all I know right now.

I am not averse to using VBA if needed, and would like to incorporate a button so the user can update the Sheet1! references (they do it manually now for hundreds of records!)

THANK YOU!

NBVC - Sorry! pdf is listed in the valid file attachments list, so I thought it was Okay.

The .xls is attached. There are multiple arrays, but please see the range B56:G77

MANY thanks for your help! My Excel expert is on vacation this week.

Thanks for the FAST response. Please see the attached 'real' workbook. I left the sheet names intact. This is the workbook I described using Sheet1! and Sheet2!.

I hope this is clear. You are very helpful.

The Salaries and Benefits sheet:
Columns A and B refers to the other sheet to display the referenced cell values.
EX:
A5 ='GEA Sal Sch'!$B$77 (and displays the value 10.0)
B5 ='GEA Sal Sch'!$E$77 (displays the value 68,887)
G5 =B5+F5

NOTE:
Salaries and Benefits! Column A always references Column B in GEA Sal Sch!, but the Row referenced will differ.
Salaries and Benefits! Columns A and B always reference the Same Row in GEA Sal Sch!

***Here is the logic I need help incorporating:***

IF the value in Salaries and Benefits! column A = 10, then Salaries and Benefits! A and B remain unchanged - keep the same references
Since A5 = 10.0, then Salaries and Benefits! Row 5: A5 will remain ='GEA Sal Sch'!$B$77
B5 ='GEA Sal Sch'!$E$77. No action taken.

IF the value in Salaries and Benefits! column A = 9.5, then Salaries and Benefits! A and B reference row increments by 1.
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.
AND Salaries and Benefits! B6 must increment one ROW from ='GEA Sal Sch'!$E$76 TO ='GEA Sal Sch'!$E$77.

ELSE
Salaries and Benefits! A and B reference row increments by 2.
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.
AND Salaries and Benefits! B7 must increment one ROW from ='GEA Sal Sch'!$F$69 TO ='GEA Sal Sch'!$F$71.


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 the. I am hoping to automate the logic and cell update as much as possible.

NBVC. Perhaps my thread should have been entered in Worksheet Functions? Whoops.

Hello NBVC. Thanks for reading! The cell references were manually entered, and they manually update them. Ouch.

The record lines in Salaries and Benefits! are for employees, and the cell references are for the # years in their position and their salary.

Row 5 is one employee record, so A5 refers to ='GEA Sal Sch'!$B$77 (for 10.0 yrs.)
A6 is for the next employee and refers to ='GEA Sal Sch'!$B$76 (for 9.5 yrs.)
A7 is for the next with with ='GEA Sal Sch'!$B$69 (for 6.0 yrs)

I can simplify this by changing the Salaries and Benefits! sheet as needed to replace all the cell references in column A with the values if it helps. The OFFSET statements shown below work. 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! I will continue testing and check for replies.

Good morning, NBVC. Thank You! I will try this. Is Morefunc only needed to work on the functions, or will the individual Excel Users also need 'Morefunc' installed in order for the changes to work? I doubt the users will need it, but I must check with you first.

I appreciate your help very much.

I applied your method and it is nearly flawless. Thank You! There are a few problems, however. In some cases, the column instead of the referenced row is decremented or incremented, changes are made even though the condition evaluates FALSE, or the returned reference seems to be random (but I know there is a reason)

I think that I understand the nice formula you provided. I really tried my best but cannot see the problem. Perhaps you can see it with these details. I do not want to seem critical - only provide you with a number of examples to help. Thanks again.

It looks like some of the evaluations where Column A = 10 are returning an incorrect reference by decrementing the column by 1.
Please see the original attachment 'Copy.xls'. B9 ='GEA Sal Sch'!$F$77 (value is 72,573). Since A9 = 10, there should be no changes made.
Please see 'Copy of Copy.xls'. When the formula is applied in C9, the reference returned ='GEA Sal Sch'!$E$77 (value is 68,887). So the column has changed from F to E. Decrementing the column.

Also, the original B15 ='GEA Sal Sch'!$D$77. The formula seems to return ='GEA Sal Sch'!$E$77 (68,887). Incrementing the column, but there should not be any change made.

There is also a problem at Copy.xls Row 37, where the reference should increment by 2 rows, but it does something kind of wild. Original B37 ='GEA Sal Sch'!$D$69. The formula seems to return either one of these three references 'GEA Sal Sch'!$F$69, 'GEA Sal Sch'!$E$71, or 'GEA Sal Sch'!$D$73 (hard to tell, because the value returned (58,916) is in three cells in the range 'GEA Sal Sch'! B56:G77

Please also see Row 71, where the reference should increment by 2, but it
The original B71 ='GEA Sal Sch'!$F$68. The formula should increment the row to 'GEA Sal Sch'!$F$70, but it seems to return one of three references 'GEA Sal Sch'!$F$68 (the same as original), 'GEA Sal Sch'!$E$70, or 'GEA Sal Sch'!$D$72. Again, it is hard to know, because the same value (57,421) is in three places.

Thank you!

Thanks again for your fast response. I changed locales last Thursday, and I still do not have remote access to all the files yet.

Sorry if I was not clear in my last reply. The new "Contract Amount" should only drop down rows and never change columns. The formulae you sent actually resulted in column jumping, and sometimes decrementing the row. I could not see why.

You are very helpful! I am planning to donate to one of your charities if we get this all worked out. If the problems I noted in my last post cannot be resolved with Morefunc, I would like to pursue this with VBA. Have a great day NBVC!