IF function

[beachy302]

I have 25 information cells, in a separate cell it will tell me which box to get my information from. I want a formula in another box to show the information from that cell. If cell A says 10, I want cell B to show the information in cell 10; if it says 25 in cell A then I want box B to show what's in cell 25... etc. I've tried using the IF function but can't quite get it correct.Thanks

 

[Joe4]

Welcome to the Board!

I think we need more details, as the question is a bit vague.
What do these "arbitrary" numbers (1-25) represent? How does it know where to pull from?

For example,

If cell A says 10, I want cell B to show the information in cell 10

When you say cell "A", what exactly do you mean? Column A? If so, which row? What is the exact range address of this "A" cell? A1? A2?
And then what exactly is cell "10"? How do we know where to look? Have you named the ranges?
Or does "10" represent row "10"? If so, which column in row 10 are we looking at?

 

[beachy302]

Welcome to the Board!

I think we need more details, as the question is a bit vague.
What do these "arbitrary" numbers (1-25) represent? How does it know where to pull from?

For example,

When you say cell "A", what exactly do you mean? Column A? If so, which row? What is the exact range address of this "A" cell? A1? A2?
And then what exactly is cell "10"? How do we know where to look? Have you named the ranges?
Or does "10" represent row "10"? If so, which column in row 10 are we looking at?

Everything's is on the same sheet and for example, the boxes I want to choose from is A1-A25. If box B1 says 1 I want to C1 to show what's in A1, if it says 10, I want it to show what's in A10 and so on...

Thanks

 

[Joe4]

You can use the INDIRECT function to build your cell reference.

If box B1 says 1 I want to C1 to show what's in A1

In this example, you would put the following formula in cell C1:

=INDIRECT("A" & B1)

So, that tells it to return the value from column A, whatever row number is entered into cell B1.

 

[beachy302]

Thanks but I think I need more for it, I'm not putting it across properly.
In cell M5 I state which cell I want information from (form 1-25 which happens to be from K22-K46).
Depending on number I put in in cell M5, I want the corresponding information fro that box to show up in box M2.

Thanks again

 

[Joe4]

In cell M5 I state which cell I want information from (form 1-25 which happens to be from K22-K46).

That is very different than what you said in your previous response. In your previous response, it seemed to imply that the row number and item number matched.

This shows why it is important to ask a good, complete, detailed question, and not just a general one. Otherwise, you will get an answer the question you posted, but it might not solve the actual problem you are trying to solve (because we do not know the important details that were left out). I tried to get those details from you in my first reply (which is why I asked all those questions).

Based on the new information, it appears that you have an offset of 21 (i.e. item 1 is in row 22, item 2 is in row 23, etc).
So, all you need to do is add that "offset" to your formula, i.e.

=INDIRECT("K" & M5 [COLOR=#ff0000]+ 21[/COLOR])

 

[beachy302]

Ok, well I'm sorry I didn't phrase the question correctly for you. It's quite. complex spreadsheet as it is and wasn't really looking for a telling off over it.

The spreadsheet is actually for calculating the release point of a parachute, so in cell M5 you put in the exit altitude from the aircraft and that then refers you to cells A22 - A46. You then type a series of information in in cells B22-B46 and C22-C46, you then get some formulae in cells D-J 22-26 and the answer is given in K22-46, depending on what number you placed in M5 in the first place.

It's a complicated question to a complicated formula, hence the loose question.

Sorry if that's complicated it even more

 

[Joe4]

and wasn't really looking for a telling off over it.

My intention was to "tell you off", but to explain why I was asking the questions I did (especially when you did not answer most of them). We do not do it to "be a pain" or be an extra burden to you, but trying to understand the question in order to give you a solution that will actually work for you. Otherwise, you do not get the answer you need and we both go away frustrated. I see you are a noob here, so just trying to explain how to get the most out of the forum. There are a few "Stickys" too with some posting tips, like this one: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Back to your question, did my last formula work for you? If not, what issues are you still having with adapting it?

 

[beachy302]

I wasn't trying to be awkward, just funny... didn't work!

The problem is I'm trying to take a spreadsheet that is quite old but way too complicated and scientific to re-do and make it work for what I need now.

The formula didn't work and it's hard to really put into words the actual problem, hence me confusing matters a bit.

In cell M5 I input the exit altitude of the parachutist, then with information that I've already input (wind direction and strength) it gives me the release distance of the parachutist in yards. (all I want to do it convert that into kms. The problem comes when the altitude changes. If I input 11 (11,000ft) into cell M5 then the result that I need is from cell K36 and if it's 22 (22,000ft) then I need the result from cell K25... and so on.

It's just the formula for the first result that I was concerned with as I'm sure I could put the formula in 25 times in the same way that I can with the IF function.

Sorry for confusing things and I appreciate the help a lot.

Scott

 

[beachy302]

using the IF function works for just one result but I need it to work with up to 25 results.

=IF(M5=2,K45) works for the one result but I need it to work with the other possible results... =IF(M5=2,K45) and =IF(M5=3,K44) and =IF(M5=4,K43) and so on.