Hello Experts,
I was working on this sheets and then I faced this issue. I want to move data from Sheet 1 to Sheet 2, where the price for each month in each year to appear the table of sheet2. for Example:
I want to know the prices for Material " 100016" and to be copied in sheets 2 in the correct month where the order is issued.
Could you please advise ?
Best,
Mohamed
Last edited by mrnassaro; 11-13-2017 at 03:11 PM.
Please attach a small sample file with representative data and expected results.
You can manually enter results.
I am attaching the sample file
In C3, copied across and down:
=IFERROR(INDEX(Sheet1!$I$2:$I$1588,MATCH(1,INDEX((MONTH(Sheet2!C$2)=MONTH(Sheet1!$C$2:$C$1588))*(YEAR(Sheet2!C$2)=YEAR(Sheet1!$C$2:$C$1588))*(Sheet2!$A3=Sheet1!$A$2:$A$1588),0),0)),"")
AFTER some changes to the dates in row 2.
I have just noticed that your profile saya Excel 2010. Do you need backward compatability with Excel 2003 and earlier (you attached the out-dated .xls)
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
This Really works Thank you so much !
Could you please explain the logic used?
Dates. They were changed to first of each month and then formatted to look like month names.
=IFERROR(INDEX(Sheet1!$I$2:$I$1588,MATCH(1,INDEX((MONTH(Sheet2!C$2)=MONTH(Sheet1!$C$2:$C$1588))*(YEAR(Sheet2!C$2)=YEAR(Sheet1!$C$2:$C$1588))*(Sheet2!$A3=Sheet1!$A$2:$A$1588),0),0)),"")
Red, when the following conditions are met (1=TRUE)
Orange: the order month matches the month of the dates in row 2 AND WHEN
Cyan: the order year matches the yearof the dates in row 2 AND WHEN
Green: the part numbers correspond the return
Blue: the prices
Yellow: this bit converts the formula from a resource-heavty array formula to one that's a bit faster.
Black: if you return an error, then put a blank.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Last edited by Glenn Kennedy; 11-13-2017 at 02:39 PM.
AwesomeOriginally Posted by Glenn Kennedy
Thank you very much !!
I edited the colour coding quite a few times, cos I messed it up... It's OK now...
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Could you please explain the Asterisk and the equal between Month Function and Year Function ?
asterisk = multiply - both have to be true
Equal... means equal!! Month in Sheet2 C2 = month in Sheet 1 C2-C1588???!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks