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.
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 *
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.
@ 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.
@ 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
I was sure that as you suggested this formula, you are sure that works.
But this getting me crazy...See my example...
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?
@ Fotis
Yours are the exact same results as mine
Are you sure your calculation is set to 'automatic'
by the way here's the additional table am talking about.
Super thanks.
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.
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
which works on the premise of finding "to" rather than "-"
Please Login or Register to view this content.
You'd have to change your table's first and last entries accordingly as well.
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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks