Building Dynamic Range using Excel Formula

0
votes

Im quite new to excel formulas, and I am trying to do something I believe should be very simple. Simplistically put, I have a range $A$8:$A11 that has data, but I only want to have a look at $A$8:A10. But $A10 can grow. If I put a value in cell A12, then the range must change to $A$8:$A11.

I have a formula where I get the row of the last cell in the table - 1(i.e. If there is data in cell A11, it grabs the row number before it, in this case 10):

=(MIN(ROW(FleetNameNumbers))+ROWS(FleetNameNumbers)-1)-1

In this case it is row 10. Again, if I put a value in A12, then this formula will change to row 11.

It is in this formula that I want to dynamically be able to change the range :

=LOOKUP(2,1/(($A8:$A11)=A11),G8:G11)

Ive tried this:

=LOOKUP(2,1/(($A8:$A11-1)=A11),G8:G11)

But I get an #N/A value. So obviously this is not allowed. Ive read about OFFSET, but I have no idea if it can work and do what I want to do ?

I am sure this is a simple thing to do, and if anyone can help, I would GREATLY appreciate it.

Thanks.

1
excelexcel-formula

1 Answers

0
votes

Typical - After posting the question, I find the answer. Thanks for anyone who had a look at my question and tried to find a solution. I am posting this to possibly help anyone else:

The formula I used to get the 2nd last row number is as follows:

This formula allows me to set the range :

=INDEX($A:$A,10) // resolves to $A$10

And as I already have a formula that gets the 2nd last row, I combined them :

=INDEX($A:$A,(MIN(ROW(FleetNumberRange))+ROWS(FleetNumberRange)-1)-1) // Gets the row number of the second last row.

FleetNumberRange is a Named range basically getting the number of rows that have data based on Column A8 onwards:

=OFFSET('Fleet Fuel Consumption'!$A$8,0,0,COUNTA('Fleet Fuel Consumption'!$A$8:$A$200),1)

So my entire formula now looks like this:

=IFERROR(G11-(LOOKUP(2,1/($A$8:INDEX($A:$A,(MIN(ROW(FleetNumberRange))+ROWS(FleetNumberRange)-1)-1)=A11),$G$8:G11)),"")

Not sure if this is going to help anyone, but thought I would post my answer.