How to: Modify formula if certain cell is blank

[JPWRana]

A31 is populated. A32 and A33 are not. (Their population will always only be numbers, and if it matters 1-80 only)
When A27 is populated, there are formulas for on O31, P31, Q31, and R31.

O31
=IF(Sheet1!J28="","",IF(AND($B31=$B30,$B30=$B29,$N31>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B30&"("&SUM(O$4,$N30,$N29)&")"&".JPG","("&SUM(O$4,$N30,$N29)&")"),IF(AND($B31=$B30,$N31>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B30&"("&SUM(O$4,$N30)&")"&".JPG","("&SUM(O$4,$N30)&")"),IF($N31>=1,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B31&"(1).JPG","(1)"),""))))

P31
=IF(Sheet1!J28="","",IF(AND($B31=$B30,$B30=$B29,$N31>=2),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B30&"("&SUM(P$4,$N30,$N29)&")"&".JPG","("&SUM(P$4,$N30,$N29)&")"),IF(AND($B31=$B30,$N31>=2),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B30&"("&SUM(P$4,$N30)&")"&".JPG","("&SUM(P$4,$N30)&")"),IF($N31>=2,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B31&"(2).JPG","(2)"),""))))

Q31
=IF(Sheet1!J28="","",IF(AND($B31=$B30,$B30=$B29,$N31>=3),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B30&"("&SUM(Q$4,$N30,$N29)&")"&".JPG","("&SUM(Q$4,$N30,$N29)&")"),IF(AND($B31=$B30,$N31>=3),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B30&"("&SUM(Q$4,$N30)&")"&".JPG","("&SUM(Q$4,$N30)&")"),IF($N31>=3,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B31&"(3).JPG","(3)"),""))))

R31
=IF(Sheet1!J28="","",IF(AND($B31=$B30,$B30=$B29,$N31>=4),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B30&"("&SUM(R$4,$N30,$N29)&")"&".JPG","("&SUM(R$4,$N30,$N29)&")"),IF(AND($B31=$B30,$N31>=4),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B30&"("&SUM(R$4,$N30)&")"&".JPG","("&SUM(R$4,$N30)&")"),IF($N31>=4,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B31&"(4).JPG","(4)"),""))))

Since A32 and A33 are blank, how can I have a macro modify
O32, P32, Q32, R32 and O33, P33, Q33, R33
such that the only modification is the first cell reference in the formulas.

Currently O32 says
=IF(Sheet1!J29="","",IF(AND($B32=$B31,$B31=$B30,$N32>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B31&"("&SUM(O$4,$N31,$N30)&")"&".JPG","("&SUM(O$4,$N31,$N30)&")"),IF(AND($B32=$B31,$N32>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B31&"("&SUM(O$4,$N31)&")"&".JPG","("&SUM(O$4,$N31)&")"),IF($N32>=1,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B32&"(1).JPG","(1)"),""))))

O33 says
=IF(Sheet1!J30="","",IF(AND($B33=$B32,$B32=$B31,$N33>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B32&"("&SUM(O$4,$N32,$N31)&")"&".JPG","("&SUM(O$4,$N32,$N31)&")"),IF(AND($B33=$B32,$N33>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B32&"("&SUM(O$4,$N32)&")"&".JPG","("&SUM(O$4,$N32)&")"),IF($N33>=1,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B33&"(1).JPG","(1)"),""))))

As O## cells go down, so does Sheet1!J## go down as well. Because A27 is the last populated instance, how can I have a macro modify

EITHER
O32 and O33 (because A32 & A33 are blank) so that in their populated formulas, Sheet1!J## have Sheet1!J27... everything else stays the same.

OR
Change the O27-R27 formulas so that Sheet1!J27 be changed to Sheet$1!$27 (fixed cell), and all other rows below A27 that are blank have the NEW MODIFIED O27-R27 copy down so that the Sheet$1!$27 will stay fixed.

[Richard Buttrey]

Hi,

I find your explanation difficult to follow, and in particular your reference to: "Sheet1!J27 be changed to Sheet$1!$27 (fixed cell)" which is presumably a typo since $27 on its own is not a valid reference.

Can you upload your workbook and manually add the expected results that you expect to see based on the given data.
It would also help if you could explain what you're trying to achieve. Don't tell us what your existing formulae are doing since there may well be more efficient ways of achieving your goal, and most of us like to go back to first principles rather than be hidebound by pre-conceived methodology.

[JPWRana]

Sure. I ran out of time for today (babysitting time), but I will do that.
It is a typo, Sheet$1!$27 IS SUPPOSED TO BE Sheet1!$J$27

What I am trying to achieve is the following.

Each structure is inspected for safety. If something wrong is found, an action item is written up for fixing. If more than 1 thing are found wrong on the same structure, then multiple action items are written up.

When writing up an action item, I can have a max of 4 photos showing what the issue is. I may not need all 4, but I don't have space for more than 4 and I need at least 1.

On O32-R32 is where I make hyperlinks for those photos. A32 is the number, like #1, #2, #3 on my batch of 80 structures.

If A?? is blank, that means that is the second row of one of the 80 structures. N32 tells me how many photos I have.

I hope this helps.