Selecting a cell value below a cell with a known value

[tomdriscoll]

Hi,

What I am trying to do is select a value (value 2), which is in the cell below another, known, value (Value 1). I have performed a calculation with Value 1, and want to now perform the calculation with Value 2. Subsequently, I will want to repeat the calculation with Value 3, 4,....n. These values are the starting point for a series of calculations in separate tables.

Currently I am trying to do this by finding the location of the cell (Value 1), which is in a different worksheet, and I know the value this cell has. I then want to select the value of the cell directly BELOW the first cell found (Value 2).

How do I do this? I would like to use formulas as opposed macro's or VBA. If you need any more detail let me know.

Thanks,

Tom.

If it helps, here is the context of the problem:

A car is accelerating in a straight line of length L. It starts at a known velocity V1, and must end at a known velocity V2. I am trying to find the quickest time the car can do this in. I am trying to find the point at which the car should begin braking (if it needs to brake at all, but let's assume that it will). I am currently trying to do this by calculating the braking time at a range of velocities, and selecting the combination which most closely matches, but does not exceed distance L when the acceleration and braking distances are added together.

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic
but you could probably just use match with index
index(sheet2!a1:a100,match(yourvalue,sheet2!a1:a100,0)+1)

[amit.wilson]

Hi - you could try this

1. find the address of the cell with the Value 1 using the function Match().

2. Use the next cell (in the row below), containing Value 2 with the functions Cell() and Offset().

If you upload a sample sheet, I could help with the formulas.

Cheers

[martindwilson]

thats not necessary if you can match the cell then you just use match()+1

[amit.wilson]

Yes that's far more elegant. Thanks martindwilson.

Cheers

[tomdriscoll]

Hi,

Thanks for the quick response. Here is a sample worksheet. Normally the data would note be on the same worksheet, and I'd like the formula to work regardless of what any of the figures are - they will change. For simplicity, I have not included the other data/calculations, they shouldn't impact on this formula anyway.

example sheet.xlsx

Regards,

Tom

[martindwilson]

you say closest is that above or below?
assumin its below
then see attached
note for any type of close match the values in col b must be sorted ascending(or possibly descending for nearest highest using match)

[tomdriscoll]

Spot on!

Thank you very much for this - many hours spent head scratching over this!!

Tom

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.