find value in row and pull through value/cell reference

Brass Contributor


I have a spreadsheet that get updated every week with new percentages for each activity. At the minute I manually check each row to see if any of the percentages have reached or are now over 50%, then once it reaches it, I simply put a formula to pull through the cell that contains this value e.g "=BU14" (the cell reference of the 50% value) and then drag this cell reference to the left to collect the weekly percentages after this point. I'm trying to find a formula that automatically looks across a row of data for each activity and then when the percentage hits 50% it automatically pulls this figure into another tab. 

The issue I have is that after the initial 50%, I then need a way of pulling through every cell in that row after that n a week by week basis.

I had tried using a helper column that find the 50% value (=INDEX('% from CASPR'!C12:ZZ12,MATCH(MIN(ABS('% from CASPR'!C12:ZZ12-'% from CASPR'!$DO$1)),ABS('% from CASPR'!C12:ZZ12-'% from CASPR'!$DO$1),0))) which pull through the nearest value to 50%, however doesn't help with the cell reference issue.

My intial thought was to use offset in some context but wasn't sure how to use it for this




1 Reply


Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?