# Populating the datasheet using the contribution table

1. ## 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.

2. ## 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

3. ## 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. ## 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?

5. ## 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

6. ## 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...

7. ## 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. ## 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. ## Re: Populating the datasheet using the contribution table

Super thanks.

10. ## 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.

11. ## 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. ## 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.

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

#### 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