LinkBack URL
About LinkBacks
Anbuselvam 
Hi,
Please find the attached copy of excel sheet. In Master sheet Row 4, 6, 7, and 9 to 12 is not showing value. Please check and correct it.
Thanks in advance.
Hi,
Please find the attached copy of excel sheet. In Master sheet Row 4, 6, 7, and 9 to 12 is not showing value. Please check and correct it.
Thanks in advance.
Hi, welcome to the forum
What on earth are you trying to do there?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
...there are no values for vitamin 12 that correspond with those dates - so it WILL return blanks!!
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi
I didn't get you!
If you see the row now 4 in Master sheet, there is product Vitamin 12 and date is 30.04.2017 which is not mention in other sheet ( Permanent, Temporary), This is the reason you are not getting the values.... Formula is working well.Originally Posted by Anbuselvam
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Explain what you are doing - and what you want. I am not going to try and pull that formula apart lol
There's little point in telling us that it's wrong, unless you tell us what you EXPECT it to do....
I only see Vitamin 12 on two rows in your data. This looks correct the way it is.
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
Hi
Thanks for all your reply!
In permanent and temporary sheet have vitamin 12 formulations on 10-May-17 and 02-May-17 respectively, My ecpected result is in the Master Sheet as below.
According to Master sheet Column A4 to A12 dates, Vitamin 12 formulation should display in C4 to L12.
Before 02-May-2017 and upto 09-May-17 temporary vitamin 12 formulation should display.
After 10-May-17 to further date until next change in the formulation from permanent or temporary by dates.
I hope you understand.
No. Please MANUALLY fill in the EXPECTED results for Ingredient 1...
Date Product Incredient 1 Incredient 2 Incredient 4 Incredient 5 Incredient 6 Incredient 7 Incredient 8 Incredient 10 Incredient 11 Incredient 12
30-Apr-17 Vitamin 12 63.87% 13.13% 2.99% 19.91% 0.01% 0.09%
2-May-17 Vitamin 12 63.87% 13.13% 2.99% 19.91% 0.01% 0.09%
5-May-17 Vitamin 12 63.87% 13.13% 2.99% 19.91% 0.01% 0.09%
7-May-17 Vitamin 12 63.87% 13.13% 2.99% 19.91% 0.01% 0.09%
10-May-17 Vitamin 12 5.00% 94.60% 0.40%
11-May-17 Vitamin 12 5.00% 94.60% 0.40%
12-May-17 Vitamin 12 5.00% 94.60% 0.40%
13-May-17 Vitamin 12 5.00% 94.60% 0.40%
14-May-17 Vitamin 12 5.00% 94.60% 0.40%
I just copy and paste the expected results above as I don't know how to attach the sheet while reply.
May be in C4:
=SUMPRODUCT(((Permanent!$A$3:$A$14=$A4)*(Permanent!$B$3:$B$14=$B4)*(Permanent!$C$2:$L$2=C$3)+ (Temporary!$A$3:$A$14=$A4)*(Temporary!$B$3:$B$14=$B4)*(Temporary!$C$2:$L$2=C$3)*Temporary!D3))
Quang PT
Please find the attachment with expected value
Check results with your suggested formulation in the attachment here. It is not working as expected.
Could you explain where and how the data come from?
In C4, copied across and down:
=IFERROR(INDEX(Permanent!C$3:C$14,MATCH(1,INDEX((Permanent!$A$3:$A$14<=$A4)*(Permanent!$B$3:$B$14=$B4),0),0)),INDEX(Temporary!C$3:C$14,MATCH(1,INDEX((Temporary!$A$3:$A$14<=$A8)*(Temporary!$B$3:$B$14=$B8),0),0)))
Last edited by Glenn Kennedy; 05-09-2017 at 03:40 AM.
Hi
Please find the attached copy. In permanent sheet i have added formulation on 05-May-2017 for vitamin12, but its not reflecting in master sheet.
As per my understand you used incredient1 column for the conditions in formula which is not having values at all.
So please check and correct it
Thanks in Advance
Upto 09-May-2017 formula should come from Temporary sheet row 3, on or after 10-May-17 formula should come from permanent sheet row 11, until further changes in vitamin 12 by future dates.
Candidate for "least comprehensible post ever"?
Is it are you looking for?
Thanks for your reply!
In master sheet row 4, 30-Apr-17 should display the formulation from near date of 02-May-17 in temporary sheet. Also now i have added formulation in 05-May-2017 in permanent sheet but its not showing that.
please check attachment.
Dear Excel Genius
For the above query, i have used below formula earlier and i got desired value. unfortunately that excel sheet i have lost and did not get back. In that i have used below formulation and it's incomplete now. can anyone check the below to complete the same for me.
=IFERROR(INDEX(Permanent!$B$3:$K$14,MATCH('Master sheet'!$A4,Permanent!$A$3:$A$14,0),MATCH('Master sheet'!B$3,Permanent!$B$2:$K$2,0)),INDEX(
Expecting your valuable support.
Thanks in advance
All of the previously attached sheets are still there on this thread. So just download the relevant one yourself....
All the previous reply is not get desired value? Can anyone suggest the formula to get my expected value.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks