lababa
11-28-2018, 03:46 AM
Hi,
I have twoexcel documents, "Book1" and "Book2". Book1 is exportedfrom our business system. Book2 is a compilation of forecast from the businesssystem data that were exported.
Today Ihave to manually put in the data from Book1 to Book2. I want to automate thatprocess.
In Book2 Ihave dates in row 25 like dec-18, jan-19, feb-19 and so on. I want these datesto be matched with the dates in Book1 that are displayed as 2019-01-05 and soon, which are displayed vertically in column S. If a match is detected I want Book2 to take the data in column R inBook1 associated with the same row of the date and place it in Book2 in thesame date.
Now I’m using a rangefor the Book1 costs and dates and use VLOOKUP for the cells in BOOK2.
Problem number 1: Is thatwhen we export the information from our business system, the cost is in the leftcolumn of the dates. VLOOKUP is trying to match the dates of BOOK2 with thecost of column R. How do I manage to change it without changing the original format?
Problem number 2: Isthat VLOOKUP is matching the dates exactly, I want it to match only the months.
Problem number 3: Iwant it to sum the costs of the same months and display it as one cost. So forexample January has 2 costs, I want it to display the sum of these costs inBook2 jan-19.
My question is: is itpossible?
Formula I'm using now is which isn't working:
=VLOOKUP(P25;(Book1.xls!planerat);2;FALSE)
Also if someonehas a better solution than VLOOKUP, comment it below ��
Thank you in Advance
I have twoexcel documents, "Book1" and "Book2". Book1 is exportedfrom our business system. Book2 is a compilation of forecast from the businesssystem data that were exported.
Today Ihave to manually put in the data from Book1 to Book2. I want to automate thatprocess.
In Book2 Ihave dates in row 25 like dec-18, jan-19, feb-19 and so on. I want these datesto be matched with the dates in Book1 that are displayed as 2019-01-05 and soon, which are displayed vertically in column S. If a match is detected I want Book2 to take the data in column R inBook1 associated with the same row of the date and place it in Book2 in thesame date.
Now I’m using a rangefor the Book1 costs and dates and use VLOOKUP for the cells in BOOK2.
Problem number 1: Is thatwhen we export the information from our business system, the cost is in the leftcolumn of the dates. VLOOKUP is trying to match the dates of BOOK2 with thecost of column R. How do I manage to change it without changing the original format?
Problem number 2: Isthat VLOOKUP is matching the dates exactly, I want it to match only the months.
Problem number 3: Iwant it to sum the costs of the same months and display it as one cost. So forexample January has 2 costs, I want it to display the sum of these costs inBook2 jan-19.
My question is: is itpossible?
Formula I'm using now is which isn't working:
=VLOOKUP(P25;(Book1.xls!planerat);2;FALSE)
Also if someonehas a better solution than VLOOKUP, comment it below ��
Thank you in Advance