Excel formula help - Advanced cell value copy

Hi All

My formula (as shown in the picture attached: untitled.jpg) copies the value from the green highlighted cell and adds an "-" and the cell value in column H. This runs for an unspecified amount of rows until a new value in column C appears.

My formula should now adapt and use the value in C567 instead of C536 for the forthcomming rows (after the yellow row 567) in column I, but as you can see it obviously doesnt. The values are still copied from C536.

Can you help on how I should rewrite the formula so it "updates" the cell-number from which it should copy the value?

NOTE: It is not allowed to add a new formula in a new column/row in this very sheet, however sheet 2 and 3 are possible to use.

Let me know if there is any confusion or information needed to solve this problem.

Thanks in advance.

Originally Posted by Fotis1991

Hi and welcome to the forum.

As you can understand, No one can work in a picture!

So, pls, upload a small sample workbook.

I can understand that, thanks.

I think a sample sheet should have been uploaded here.

Thanks

Formula in column I should be changed.

Goal is that the numbers in column I change value when i insert a new value in column C.

As you can see all values in column I lead back to cell C1. I want the numbers after ROW 32 in column I to refer to cell C32, and i want it to happen automatically if i were to insert another value in column C.


Let me know if its still confusing.

Thanks

Originally Posted by Fotis1991

I think it's clear now.

So in I2 and copy down, use this formula.

=IF(H2="","",INDEX($C$1:C2,MAX(MATCH(REPT("z",255),$C$1:C2)))&"-"&H2)

Is this, works for you?

Wauw,

Your'e a genious !

Thanks so much. My job just got 50% easier thanks to you.

Have a nice day,

Thanks

Can you explain what the addition you made does specifically?