OFFSET: stay in range

[bricem]

Hello,


I need a chart which predicts when a product is ready for harvest.

production date + amount of grow days = Production output date

For this I use the OFFSET function

The issue is, as I have to use a negative number to reference the growth date in the past, I get results out of the range C3:H5.
Screenshot_1.png


How do I limit the OFFSET function to the range C3:H5?


Any help is greatly appreciated.

[Glenn Kennedy]

Rather than showing the results of a formua that does not work... please tell us WHAT you expect to see and WHERE you expect to see it...

[bricem]

This is how it should like:
Attachment 740621

[AliGW]

Please attach a WORKBOOK, not a picture of one. Thanks.

[davsth]

Im with Glenn, but what are you wanting to achieve. Mock up the results you desire and Im sure we can give you a solution

perhaps =IF(COLUMN()-$B3>2,OFFSET(E3,0,-$B3),"")

[bricem]

I want to reference to cells using the offset function, EXCEPT when the referenced cell is in column A,B, or outside the Workbook.

[davsth]

isn't that what I gave you?
=IF(COLUMN()-$B3>2,OFFSET(E3,0,-$B3),"")

if you want 0 and not blank
=IF(COLUMN()-$B3>2,OFFSET(E3,0,-$B3),0)

[bricem]

Yes thank you, I had to adjust it to
=IF(COLUMN()-$B3>2,OFFSET(C3,0,-$B3),"")
(in CELL C9)

Now it works!
Thank you very much!

[davsth]

E9 was the cell you had highlighted in the screenshot saying it was not working hence the formula I gave you was for. I f you were starting in column C it was the formula you adjusted it to. although if you look in column E the formula will be what I gave you. it could have been copied down and left and right to give the results you have

Glad it worked