0

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.

Improve this question

1 Answer 1

Reset to default
0

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.