Multiple cases Macro

Hi Everyone,

Glad to be part of the guru's forum. I have a task on which I'm struggling on and wasn't able to find any solution on that. I want to populate one specific cell, based on the value of two other cells that show the region and the FY. I can do that with four different IF statements but it doesn't work when I try to do it with macros. So, let's say we have a drop down for Regions, a dropdown for Fiscal Years and for each and every combo, there is distinct value with which I want to populate one specific cell. Therefore, everytime the user changes either the Region or the FY the value that will be returned, will change.

The IF functions I have and work individually are:


=IF($F$2="EMEAR",IF($J$2="FY17",Dropdown!D31/1000,IF($F$2="EMEAR",IF($J$2="FY18",Dropdown!E31/1000,IF($F$2="EMEAR",IF($J$2="FY19",Dropdown!F31/1000,))))))

=IF($F$2="AMERICAS",IF($J$2="FY17",Dropdown!D30/1000,IF($F$2="AMERICAS",IF($J$2="FY18",Dropdown!E30/1000,IF($F$2="AMERICAS",IF($J$2="FY19",Dropdown!F30/1000,))))))

=IF($F$2="APJ",IF($J$2="FY17",Dropdown!D32/1000,IF(F2="APJ",IF($J$2="FY18",Dropdown!E32/1000,IF($F$2="APJ",IF($J$2="FY19",Dropdown!F32/1000,))))))

=IF($F$2="GRTR CHINA",IF($J$2="FY17",Dropdown!D33/1000,IF($F$2="GRTR CHINA",IF($J$2="FY18",Dropdown!E33/1000,IF($F$2="GRTR CHINA",IF($J$2="FY19",Dropdown!F33/1000,))))))


I tried to create 4 different macros and another one which will run all the other four, and assigned the latter to a button which when clicked will run them all. Although it worked in separate cells, I can't fine a way when I click the button to populate the returned value to ONE specific button. Like, replacing the value according to the criteria met.

Hope it makes sense, and I'd appreciate any help .

Regards,
H

Hi dflak,

It was resolved with index and match indeed. It's more like a two way lookup

I am confused Are you trying to change the text of a button? If so, then here is the code to do that

Please Login or Register  to view this content.

Else it would be better to upload a sample so that I can be clear about your requirements.