Need Help with Indirect function combined with Index/Offset/Address function in excel

[Ria_Ko]

Hello everybody! Hope all are well.

I need some help in filling data in a particular table. I have tried multiple times to get the right formula, tried to seek help from youtube videos but none of it helped me get through.
In the worksheet "Part1" I want to write a formula that uses indirect combined with index/offset/address function in cell J23 that can be dragged across and down J23:M25 to return the exchange rate for the desired currency (J20), year (column I) and month (row 22), and that will update when a new currency is chosen at J20. The years and months needed will change depending on what currency is selected.

The data has to be filled in "Part1" from the 5 worksheets namely, "2012","2013","2014","2015","2016".
I have attached the file for the same.

Any help would be appreciated.


Thankyou,

Ria

 

[sparky2205]

Hi Ria_Ko,
I hope I have understood your requirements correctly. Try this:
=INDEX(INDIRECT("'"&$I25&"'!B2:D13"),MATCH(M$22,INDIRECT("'"&$I25&"'!A2:A13"),0),MATCH($J$20,INDIRECT("'"&$I25&"'!B1:D1"),0))
I have set up my worksheet in the same way as yours in the screenshots so all references should match.

 

[Ria_Ko]

Hi Ria_Ko,
I hope I have understood your requirements correctly. Try this:
=INDEX(INDIRECT("'"&$I25&"'!B2:D13"),MATCH(M$22,INDIRECT("'"&$I25&"'!A2:A13"),0),MATCH($J$20,INDIRECT("'"&$I25&"'!B1:D1"),0))
I have set up my worksheet in the same way as yours in the screenshots so all references should match.

Hi sparky2205,
Thanks alot for trying to help me but the formula is returning a reference error. I am sure the formula should be somewhere near to the correct one. I will try to get to the correct one.

 

[Ria_Ko]

Hi sparky2205,
Thanks alot for trying to help me but the formula is returning a reference error. I am sure the formula should be somewhere near to the correct one. I will try to get to the correct one.

It is returning the same value when i pull it across and down.

 

[Ria_Ko]

It is returning the same value when i pull it across and down.

and also, the value retrieved for February 2012 in J23 is incorrect according to the 2012 worksheet.

 

[sparky2205]

Strange,
I created a few sheets based on your screenshots so that you wouldn't need to adjust any cell references in the formula.
It works fine for me. It must be something simple.
Is it possible to upload your file to tinyupload.com and send a link so that I can take a look?
Make sure you purge any sensitive information first.

 

[Ria_Ko]

Strange,
I created a few sheets based on your screenshots so that you wouldn't need to adjust any cell references in the formula.
It works fine for me. It must be something simple.
Is it possible to upload your file to tinyupload.com and send a link so that I can take a look?
Make sure you purge any sensitive information first.

Hi, here is the link TinyUpload.com - best file hosting solution, with no limits, totaly free

 

[sparky2205]

My bad,
I used an abbreviated form of your spreadsheet, just to get it working.
So, instead of having 10 currencies, I only used 3. But I forgot to adjust the formula for a larger number of currencies before sending it to you.
Try this now and it should work:
=INDEX(INDIRECT("'"&$I23&"'!B2:K13"),MATCH(J$22,INDIRECT("'"&$I23&"'!A2:A13"),0),MATCH($J$20,INDIRECT("'"&$I23&"'!B1:K1"),0))

 

[Ria_Ko]

My bad,
I used an abbreviated form of your spreadsheet, just to get it working.
So, instead of having 10 currencies, I only used 3. But I forgot to adjust the formula for a larger number of currencies before sending it to you.
Try this now and it should work:
=INDEX(INDIRECT("'"&$I23&"'!B2:K13"),MATCH(J$22,INDIRECT("'"&$I23&"'!A2:A13"),0),MATCH($J$20,INDIRECT("'"&$I23&"'!B1:K1"),0))

Hi sparky2205,
It worked! Thank you so much for your help. Had been trying to get the right formula the entire day. Need to practice more of indirect function. Once again thanks alot. Take Care.

 

[sparky2205]

You're welcome Ria_Ko. Glad I could help.
I've gotten so much help myself over the years from this forum, it's good to give back when I can.
Have a good day.