Hi,
I'm trying to write a formula to put a value in a certain cell if two cells match. But the problem is that when i export the data from out business system, the data differs a bit.
For example the data in column D is :
HA01 0370
HA03 0380
HB01 0210
HB02 0300
I have attached excel file with the sheets and desired results.
I want a formula to only pick up data with HA(the numbers after is not important) in column D and only 06 in column B in Book1. How do I do that?
Last edited by lababa; 12-05-2018 at 08:43 AM.
What's your formula?
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
I have attached excel file to the thread with example and desired results. I would like the formula to pick up all values in Column K Book1 and sort it in Sheet1 row 30 by the dates depending on the date on Column E Book1. Also I want the formula to only pick HA fron column D and only pick the number 06 in column B. Is it possible?
I have this formula to pick from column K in Book1 and sort it by date in row 30 Sheet1.
=SUM(IFERROR(Book1!$K$3:$K$999*(MONTH(Book1!$E$3:$E$999)=MONTH(M$25))*(YEAR(Book1!$E$3:$E$999)=YEAR(M$25)); ))
The problem is that it picks everything from the column in Book1. I want it to only pick everything that starts with HA in column D and 06 in column B. (Make sure to enter "crl+shift+enter" before you close the cell when entering this formula)
Please try at K30 with CSE
=SUM(IFERROR(Book1!$K$3:$K$999*(MONTH(Book1!$E$3:$E$999)=MONTH(K$25))*(YEAR(Book1!$E$3:$E$999)=YEAR(K$25))*(LEFT(Book1!$D$3:$D$999;2)=$B30)*(Book1!$B$3:$B$999="06"); ))
Hey Bo_Ry you are the man!!! Thank you again! :D
Hey Bo_Ry! Could you help me with one more thing. Which formula do I use so when I pick payment terms of 30 days or 60 days from the drop down list on C29, the values from row 30 gets copied and get an offset depending on how many days I chose on the drop down list. I attached desired results in this reply. Thank you so much again!!
Please try
D29 copy to the right
=IFERROR(INDEX($D$30:$AM$30;MATCH(EDATE(D$25;-LEFT($C29;2)/30);$D$25:$AM$25)); )
or
E29
=INDEX(C30:E30;3-LEFT($C29,2)/30)
Last edited by Bo_Ry; 12-05-2018 at 10:46 AM.
Thank you again, works like a charm!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks