+ Reply to Thread
Results 1 to 13 of 13

Hidden fields in table for user in Excel front-end and java back-end

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Hidden fields in table for user in Excel front-end and java back-end

    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
    Attached Files Attached Files
    Last edited by jaryszek; 04-22-2019 at 03:54 AM.

  2. #2
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Hidden fields in table for user in Excel front-end and java back-end

    Hi,

    has anybody any idea?

    Jacek

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Hidden fields in table for user in Excel front-end and java back-end

    As you're asking how to read Excel spreadsheets in Java, I suspect you aren't going to get an answer here, Especially without you having posted any code, you might be better off asking somewhere like Stack Overflow

  4. #4
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Hidden fields in table for user in Excel front-end and java back-end

    thank you Kyle.

    Maybe i wrongly explained. I wanted to give you overall understanding of goal here.

    The issue is only in Excel.
    How to have table with one value shown for user but read this table (or mirror table) with lookup value for Back End - it can be done in any other programming language. It can be consume bu java, python or others, workbokk is just read by them.

    So if user see "Poland" in table1 in 2 row BE should consume the same table but see "PL" there.
    I am not java programmer and want to avoid calculations in java - just reading ready variables.

    So maybe in Java Script you would use something like TAG in code and have metadata for cell. In Excel it is not possible.
    I can add comments but it would be very strange i think for the user to see.

    I am seeing only one solution for now:
    1. Wrote code for all of changes.
    So when user is inputting data into table into row 3 vba is catching it and making calculations (vlookup) to fill up mirror table for BE.
    But this can be very awful solution and overal performance can be wrong.And has to include all catching mechanism for adding and deleting rows from listobjects.
    Last edited by jaryszek; 04-23-2019 at 04:48 AM.

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Hidden fields in table for user in Excel front-end and java back-end

    I've got no idea what you actually want. This doesn't make any sense:
    How to have table with one value shown for user but read this table (or mirror table) with lookup value for Back End
    Are you asking how to have 2 tables, one for the user and one on another sheet?

    This other table should have one row for every row in the first table?

    This other table may have more columns with information in that come from somewhere else?

    Your workbook isn't at all helpful or illustrative

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Hidden fields in table for user in Excel front-end and java back-end

    Right, I've had another read of this, can't you just use VLookup in your table to get the country code?


    Can't you just have one table with a hidden column for the country code?

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Hidden fields in table for user in Excel front-end and java back-end

    thank you Kyle.
    Maybe with hiding column is the best solution here. But my boss is complaining that you see this little column sign:

    Attachment 621161

    in old Excel version you didnt see it and know user can try to unhide this because of curiosity.

    Jacek

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Hidden fields in table for user in Excel front-end and java back-end

    I can't see that attachment, it's invalid

    Tell him to ignore it

    Does it really matter if the user unhides it and sees the code, I bet they won't and if they did, they won't care

  9. #9
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Hidden fields in table for user in Excel front-end and java back-end

    thank you Kyle123 smart *** ;-)

    Hmm
    I can't see that attachment, it's invalid
    i had issues with forum last times when i am attaching images....

    Best,
    Jacek

  10. #10
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Hidden fields in table for user in Excel front-end and java back-end

    Quote Originally Posted by jaryszek View Post
    thank you Kyle123 smart *** ;-)
    i had issues with forum last times when i am attaching images....
    I've got same issue last time.
    Edit post, 're-attached' picture. It helped in my problem.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  11. #11
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Hidden fields in table for user in Excel front-end and java back-end

    Where can i notify error?

    Ali are you here?

    Jacek

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Hidden fields in table for user in Excel front-end and java back-end

    Ali isn't going to be able to do anything, there's either a problem with the site or the way you're uploading the documents. Probably the former, the mods have no control over this and the forum owner just ignores everyone.

  13. #13
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Hidden fields in table for user in Excel front-end and java back-end

    Kyle123 thank you.

    I am uploading images the same since i am here.

    Someting is broken.

    Returning to the topic - my boss accepted solution with hidden columns,

    thank you for your help and support!

    Best,
    Jacek

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. download and set table in excel from web that uses Java script
    By lubbamkt in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 01-08-2016, 09:37 AM
  2. [SOLVED] create name badges with same data front and back from Excel list
    By cprpacific in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-29-2015, 02:16 PM
  3. Replies: 5
    Last Post: 11-07-2013, 02:48 PM
  4. Implement back end DB whien using excel as front end DB
    By Snoopy2003 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-30-2012, 06:39 PM
  5. Access Front-end, Excel Back-end
    By dreicer_Jarr in forum Excel General
    Replies: 3
    Last Post: 03-08-2010, 08:07 PM
  6. I can not get excel to print on front and back can anyone help me.
    By de_stap in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-01-2006, 02:40 PM
  7. [SOLVED] RE: How can I print on the front and back of a page in excel?
    By Jonathan Cooper in forum Excel General
    Replies: 0
    Last Post: 09-29-2005, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1