Search code examples
excelautomationcellvba

MS excel: expressions automatic read value from a cell?

I hope to explain what I need in the right way.

I have to work with a huge number of data. Those dataset are ordered in the same way and I need to take the same info from all.

The problem is that I am not able to set the cell in the formulas without editing all the formulas.

In the picture you can see that the dataset start at the line 74 and it involved 7 lines total

the formula is easy: =max(79:85) but I am not able to find an automatic way to set the interval. The first item is the line "(start_dataset_line+5)" and the last one is "(first_item_line+total_numer_line)" The formula shoud be =max(C$"valueinthecellB74+5":C$"valueinthecellB74+5+valueinthecellC74")

but I do not know how to write the non fixed bold expression

Is it possible to set in automatically even with a macro? Thanks

example

Solution

  • you should use the INDIRECT function to calculate the range.

    In your case

    =MAX(INDIRECT("C"&B74+5&":C"&B74+4+C74))

    Buona fortuna con Excel :)