Advanced Formula

[Coded552]

I have a time entry sheet with a formula that works but its not very elegant (ex2):
=IF(OR(F2="",G2<>""),IF(OR(F3="",G3<>""),IF(OR(F4="",G4<>""),IF(OR(F5="",G5<>""),IF(OR(F6="",G6<>""),IF(OR(F7="",G7<>""),NOW()-TODAY()+((8-H8)/24),F7+((8-H8)/24)),F6+((8-H8)/24)),F5+((8-H8)/24)),F4+((8-H8)/24)),F3+((8-H8)/24)),F2+((8-H8)/24))

The base of this formula is:
=B3+((8-D8)/24)

But B3 is not always the cell I want to reference. I want to reference the cell on the left where the cell to its right is empty. In (ex1) the cell I want to reference is the one with 9:00 AM (yes this is B3).
D8 will always be a static reference.
I have a formula that finds the cell I want:
=ADDRESS(ROW(B2)+COUNT(C2:C7),COLUMN(B2))

But if I combine the formulas:
=ADDRESS(ROW(B2)+COUNT(C2:C7),COLUMN(B2))+((8-D8)/24)

It comes up as an error. Im assuming that there is something simple that Im overlooking but I cant think of what it could be.
Any help would be well helpful.

Thanks

 

[tyija1995]

Hey, you just need an INDIRECT function nested where you substituted in the B3 part.

=INDIRECT(ADDRESS(ROW(B2)+COUNT(C2:C7),COLUMN(B2)))+((8-D8)/24)

 

[Coded552]

Hey, you just need an INDIRECT function nested where you substituted in the B3 part.

=INDIRECT(ADDRESS(ROW(B2)+COUNT(C2:C7),COLUMN(B2)))+((8-D8)/24)

That did the trick, thank you