+ Reply to Thread
Results 1 to 12 of 12

Populating the datasheet using the contribution table

  1. #1
    Forum Contributor
    Join Date
    01-16-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2007
    Posts
    105

    Populating the datasheet using the contribution table

    Hi everyone

    need help in populating my data sheet in sheet 1 (list of employees) under the column E,F and G with a basis of the contribution table (sheet2) each contribution is based on the basic salary of an employee.

    How can I do this using excel 2007? need a formula for this.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Populating the datasheet using the contribution table

    In E4 try this array formula

    =INDEX('contribution table'!D$5:D$33,MATCH($D4,TRIM(LEFT('contribution table'!$B$5:$B$33,FIND("-",'contribution table'!$B$5:$B$33)-1))*1,1))

    Confirm with Ctrl+Shift+Enter and not just Enter

    Copy down and across
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Contributor
    Join Date
    01-16-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Populating the datasheet using the contribution table

    Thanks a lot Ace_XL.

    I got 2 questions:

    1. how or when to use your last instruction by "confirm with Ctrl+Shift+Enter" ?
    2. Can this be used in the excel database?

    Thanks again.

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Populating the datasheet using the contribution table

    @ Ace_XL

    Does your very good formula, gives you the correct results when you drag down from D4?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Populating the datasheet using the contribution table

    @ Gerard

    Ctrl+Shift+Enter needs to be used for array formulas. See here
    http://www.cpearson.com/excel/ArrayFormulas.aspx
    Yes, it can be used in an excel 'database'

    @ Fotis
    I reckon it does. See attached
    Attached Files Attached Files

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Populating the datasheet using the contribution table

    I was sure that as you suggested this formula, you are sure that works.

    But this getting me crazy... See my example...
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    01-16-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Populating the datasheet using the contribution table

    super thanks. yes it works perfectly. i got another and last question. i tried adding another type of table (in sheet 3) and i used your formula and procedure, but the results is #N/A? how's that?

  8. #8
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Populating the datasheet using the contribution table

    @ Fotis

    Yours are the exact same results as mine

    Are you sure your calculation is set to 'automatic'

  9. #9
    Forum Contributor
    Join Date
    01-16-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Populating the datasheet using the contribution table

    by the way here's the additional table am talking about.

    Super thanks.
    Attached Files Attached Files

  10. #10
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Populating the datasheet using the contribution table

    Hm.....Now i am getting more crazy my friend...

    Look what i see in my example...

    Edit: In your attached sample everything is ok!...


    @ gerard_gonzales33

    Apologize for the nuisance.
    Attached Files Attached Files

  11. #11
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Populating the datasheet using the contribution table

    Intriguing indeed.. When I open your worksheet it shows all rows as 910 | 10 | 500 (just as in the screenshot) but when I click 'enable editing' (probably a 2010 version feature), the correct results pop up.

    My guess is it has to do something with your 'calculation' mode being on manual.

    @ Gerard
    for your 2nd worksheet use this

    Please Login or Register  to view this content.
    which works on the premise of finding "to" rather than "-"
    You'd have to change your table's first and last entries accordingly as well.

  12. #12
    Forum Contributor
    Join Date
    01-16-2012
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Populating the datasheet using the contribution table

    Thanks for all the help and clarifications.

    @ Ace_XL, my apology but I didn't get your last instruction regarding the 2nd worksheet. Are you saying I have to change/ update the formula for the 2nd worksheet from "-" to "to"? but so far the formula gives me the right result and if I use the updated formula is gives me #N/A. Or the updated formula will be used for the workers share (sheet3)?

    hope you could clear me out. Thanks so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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