Hi,
i have table like here:
Screenshot_4.png
and lookup table like here:
Screenshot_5.png
In my case all Excel tables would be passed to Java Back-End (java apache poi is reading this).
Now i am using named ranges and this is working like a charm because user is inputting only one variable and formula in hidden sheet is lookup for specific BE Value.
So this is relationship 1:1 i can say.
The issue shows up when i want to have table in order to input a lot of variables set ups on one time.
Like in the example user can input 3 regions into table rows and not has to use 3 workbooks for this set up like i have now.
User will see only first worksheet "Source table".
When user is choosing Region like Argentina i have to use for BE value from worksheet "back end values" from column "BE Values", in this case "AR".
So user is selecting "Argentina" but i have to somehow say to my BE use "AR".
So in this case i have to pass to BE Values :
Region: PL, AR, UR not what user is seeing (Poland, Argentina, Urugwaj).
How can i automate this?
I could have 2 tables like in workbook attached and calculate formula based on specific row which user is selecting but problem will be when user will delete rows from first table or add new rows.
Write VBA for all of these issues?
Or maybe i can do this in easier way?
Best,
Jacek
Bookmarks