Forum Discussion
Solved
Excel function
I can't figure out how to do this. I need to put a function where
when i select Jan from the drop-down in Cell B9 it corresponds
with another sheet (Named Data) D21
I tried using this but it's not working
=IF(B9="Jan",INDIRECT("Data!D21"),IF(B9="Feb",INDIRECT("Data!E21"),""))
If someone can please advise.
INDIRECT can be a tricky function to use; even after multiple uses I find I often still need to "play around" a bit before getting it to do what I expect.
That said, I'm not clear on what you're trying to do.
- What, for example, is actually in the tab labeled Data! and cells D21, E21, etc?
Finally, assuming you actually have all 12 months, or will have, as part of this, you might find it more convenient, certainly easier to write and maintain, if you were to create a two-dimensional table and use VLOOKUP (or if it gets more complex, XLOOKUP) instead of nesting multiple levels of IF.
INDIRECT can be a tricky function to use; even after multiple uses I find I often still need to "play around" a bit before getting it to do what I expect.
That said, I'm not clear on what you're trying to do.
- What, for example, is actually in the tab labeled Data! and cells D21, E21, etc?
Finally, assuming you actually have all 12 months, or will have, as part of this, you might find it more convenient, certainly easier to write and maintain, if you were to create a two-dimensional table and use VLOOKUP (or if it gets more complex, XLOOKUP) instead of nesting multiple levels of IF.
- Sounds to me like he wants =INDEX(Data!D21:O21;1;MONTH(DATEVALUE("1-"&B9&"-1900")))
- As is always the case with Excel, there are multiple ways to get from A to B. That's just part of what makes it so much fun to be in this forum!!
