Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Date Match between column and cell to return value in third column

0

Hi, Would appreciate any help on this - even though I feel like the solution is staring me in the face.

I have one worksheet with two tabs. In one tab, there is a customer list of items, the date they want them and how many they want. In the other tab is a rough calendar.

The goal is to provide a print out of the calendar with all the customer items along with the qtys they would like for each date.

So, I would like to input a formula into the calendar tab that searches the 1st tab for a date, then returns the item and the qty required into the calendar.

I've tried VLookup, but I must be doing something wrong. I found a similar question in the old forum, but was unable to view the file solution, so hoping someone here can help me out.

The attached file may help to explain what I am trying to attempt.

Answer
Discuss

Answers

0

This is a little complicated. You must be precise!
In your calendar, select the range L12 and drag down to extend the range to L12:L17, meaning all "item" rows are selected and L12 is active. Now paste this formula.

=IF((ROW()-ROW(K10))<(COUNTIF(Dates,K10)+2),IFERROR(INDIRECT(ADDRESS(MATCH(K10,Dates,0),4,,,"Order Report")&":"&ADDRESS(MATCH(K10,Dates,0)+COUNTIF(Dates,K10)-1,4)),""),"")

It's an array formula. Therefore you must confirm it with Shift+Ctl+Enter (instead of the customary, singular Enter). If you already pressed Enter, select L12 again, drag down, press F2 and then Shift+Ctl+Enter. Now every cell in range L12:L17 has the same formula in it. You can't ever edit just one of them, but editing one will change them all.

Repeat the same thing with range K12:K17. You can't copy from column L because your cell formats are different for these columns and because both columns must pick the date from K10.

Next you should modify the formula in K12. It has two references to column D in sheet ''Order Report'. That is where it picks the order quantity. Excel numbers the columns. Column D = column 4. The two 4s in the formula are the two references to column D. Select K12:K17, press F2 to enter edit mode and change both 4s to 1. This formula will now draw values from column A in the Order Report, the item numbers.

Despite your best offort, I expect that you will see absolutely nothing being pulled from the Order Report. That is because the formula refers to the 'Dates' range which isn't set up. 'Dates' is a named range which must start from row 1 on the Order Report sheet and end at some ridiculously high number like 100 or 500 or 1000 - just any number which will definitely be greater than you ever will have rows in your sheet. Select 'Define a name' from the riboon's Formula tab or use the Names Manager.

To complete the installation, you can copy the range K12:L17 to M12:N17 or even "wholesale" to A20:N25. I noticed that on 10/18 there are 6 entries in your Order Report. If there are more than 6, the extra ones will not be copied to the calendar because there are only 6 formulas to pull the data.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login