Why isnt this formula working when I change the ranges?

[Marq]

I have a formula in a workbook that was made for me by someone a decade ago : =OFFSET($A$37,0,MIN(IF($K39:NC39>0,COLUMN($K$37:$NC$37)-1)))

Row 37 is a date range row...(i.e. 1-1-19 through 12-31-19)......rows 39 on down will contain values (man hours)

What it does is, if i plug in a value under a specific date, that date shows up in the cell the formula is in. For example the formula is in A39.....I plug in 18.5 in cell M39 under a column that has the date of 5-1-19 in cell M37. When I plug in the 18.5 then cell A39 populates with 5-1-19. So the formula works as it should.

I have two questions about it.

1- What i am not understanding is why is A37 in the formula???? There is nothing in cell A37. A37 is nothing the work sheet and no other formula references A37..its a useless cell in this workbook.

2 - I transferred this formula to a different work work book which utilizes the same formula but different rows and columns.....this is how the formula looks in the new work book (exactly the same formula..just different row and column references) =OFFSET($A$2,0,MIN(IF($T30:ZZ30>0,COLUMN($T$2:$ZZ$2)-1)))...(row 30 being my man hour row and row 2 being my date range row,

I put the A2 ref in there to match the A37.....the ONLY thing I can see the purpose of A37 is its the same row that the date range is on....so since row 37 is the date range row in the original formula I made A2 the reference in the new workbook formula since row 2 is the date range row.

 

[RoryA]

A37 is the start point for the offset formula. It then finds, from there, the first cell in columns K to NC of that row that is greater than 0. IMO it would be better to avoid OFFSET and use something like:

=INDEX($K$37:NC$37,MATCH(TRUE,$K39:NC39>0,0))

 

[Marq]

A37 is the start point for the offset formula. It then finds, from there, the first cell in columns K to NC of that row that is greater than 0. IMO it would be better to avoid OFFSET and use something like:

=INDEX($K$37:NC$37,MATCH(TRUE,$K39:NC39>0,0))

I copied and pasted your formula in place of my original formula and I am getting the #VALUE! error

 

[RoryA]

Did you remember to array enter it with Ctrl+Shift+Enter?

 

[Marq]

Did you remember to array enter it with Ctrl+Shift+Enter?

oops..haha...it works now..thanks!

 

[RoryA]

Glad to help.

 

[Marq]

Glad to help.

how do i make the cell the formula is in show up as blank instead of the #N/A result.

The formula works as it should but i want to get rid of th#N/A

 

[MARK858]

Wrap it in an IFERROR function...
=IFERROR(INDEX($K$37:NC$37,MATCH(TRUE,$K39:NC39>0,0)),"")

 

[Marq]

Wrap it in an IFERROR function...
=IFERROR(INDEX($K$37:NC$37,MATCH(TRUE,$K39:NC39>0,0)),"")

that works..thanks everyone for the help!