beachy302

New Member
Joined
Mar 19, 2019
Messages
8
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
 
=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.
This is very confusing. This appears to be quite a bit different than the first few posts. If there is any sort of predictable "pattern", then we can probably do what you want. I am just trying to understand what this pattern is.

Based on this most recent post, it looks like it is going backwards, i.e. as the value of M5 increases, the row from column K decreases, in a one-to-one relationship. If that is the case, then you should be able to use this variation:
Code:
=INDIRECT("K" & (47-M5))
Does that work?

Hopefully, you are beginning to see the pattern of these indirect formulas. Essentially, we are just building the range reference we want to pull.
The issue is, if we just do it like this:
Code:
="K" & (47-M5)
if M5 is 2, that formula will return the literal value "K45", not the value from that cell.

What surrounding that calculation by the INDIRECT function does is tell Excel that I want to return the value from the cell reference that this calculation returns.
Make sense?
 
Last edited:
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Thanks but no that doesn't work. I think I'm confusing the situation more with how complicated the spreadsheet is already so I've started a new sheet to just figure this out.
In column A I have numbers 1 - 26 in each row (A1 is 1, A26 is 26)
In column B I have letters A - Z in each row (B1 is A, B26 is Z)
In cell C1 I will input the number that I want to reference (1 - 26)
In cell C2 I want the formula to tell me the corresponding letter to the number

How does that sound?

Thanks again for your patience
 
Upvote 0
That's it, I was able to then change the formula to work in the more complicated sheet. Thanks so much for your help and patience!
 
Upvote 0
You are welcome.
Glad we got it all sorted out.
:)
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top