Hi All,
I had faced same trouble earlier, and someone here had solved the same best way.
Now i have similar Problem that i had earlier, but this time one more criteria has been added to it.
The details will be found out in attached Excel Sheet.
Thnx.. In Advance.
Last edited by andy9988; 09-17-2010 at 11:42 AM.
Here's what I did (attached).
First I named all your tables i.e. _0_35Yrs is B6:B11. Then I created a little Table for it in Sheet1!G3:H9. I use a VLOOKUP in Sheet1!B17 to figure out which table to use.
In Sheet1!D2 dragged down, the formula for calculating the Premium becomes
=INDEX(INDIRECT($B$17),MATCH($B$16,UNITED!$A$6:$A$10,0),MATCH($C2,UNITED!$B$5:$O$5,0))+INDEX(INDIRECT($B$17),6,MATCH($C2,UNITED!$B$5:$O$5,0))*$B$18
Questions?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thank U Sir,
But the formula doesn't work for 2 adults.. i.e. (1 adult + 1 adult) in United Sheet.
Otherwise it works nicely..
The answer of 3rd question still remains unsolved i.e making cells blinking for a particular criteria..,
Any ways thnx once again, but plz try to solve the problem when there is no child in family.
Wait.. y the same formula is not working in my personal excel sheet where i want to use.???
it shows the error of #REF!
Where can be the Problem ?
Last edited by andy9988; 09-15-2010 at 01:11 PM.
Why don't you show the link to the previously solved problem so that we may see if there is an easy "fix" to accomodate the new conditions.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I tried to search the same but i couldnt find the same.
it was the calculation of parents + Child , but this time premium is to be calculated for additional child.
Earlier following formula was suggested.
=IF($E$44>=46,0,INDEX(INDEX(FFG!A:A,MATCH($E$42&","&$E$43,FFG!A:A,0)):INDEX(FFG!D:D,MATCH($E$42&","&$E$43,FFG!A:A,0)+6),MATCH(C26,FFG!$A$5:$A$8,1)+3,MATCH($E$44,FFG!$B$4:$D$4,1)+1))
To fix the 1A + 1A problem, fix the formula in B16 to
=IF(AND($B$12=2,B13=0),"1A + 1A",$B$12&"A + "&MIN($B$13,2)&"C")
Did you name the tables in your worksheet?
The easiest way to name the table is to select the data range (not including vertical and horizontal headers) and in the name box (the white box that typically has the active cell i.e. A1) type the table name exactly as it appears in the VLOOKUP Table . Questions?
i have already solved the 1 + 1 Problem i have changed 1a + 1a in "United" Sheet to "2A + 0C" den there is no problem, but now the Problem is there, the formula is not working in my actual working sheet.
I am attaching the same. Password to open the same is "notfound".
Please help me in making it work in my own worksheet.
Data Input sheet will be the 'Floater" Sheet and sheet "UN" is also there.
It was, as I suspected, that you didn't have named tables on the UN sheet. I put them in, as I outlined in my earlier post. Let me know if there are further problems.
Thnx Finally it is working. It took lot of time to understand wht u said.
I couldnt get tht the message i.e naming the table. Now i ve got it and done the same.
Now it is working..
Is there any easy way to make cells blinking ????
I dont know any thing about MACRO..
Thnx Anyways..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks