• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need data validation in 3 cells and then a vlookup

D

dgoscinski

New Member
I have a scorecard template. I want a data validation to select the Name (b3). I want a data validation to select which quarter(c4). And a data validation to select the year(d4). (I have created those.) The data is kept on another sheet: QTR4-Testing!

I then want a formula (likely if + vlookup) to check the data range and automatically fill in the scorecard template for the selected name, quarter, and year. Currently, my formula is : =VLOOKUP($B$3,'QTR4-testing'!$A1:$X130,7,FALSE). This works properly when I select the Name from the list, it fills in the scorecard correctly--except that it is the first set of data. But it is only dependent upon one variable: B3 (name). I am not sure how to add Quarter and Year into the function.

I hope I am clear in my explanation. Again, select Name, Quarter, and Year THEN the entire data set is searched and results posted.
 
I have uploaded the file.
There are two worksheet. One is the scorecard template. (Scorecard) The other is the master data list (QTR4-testing).
On the Scorecard, I want data validation for B3, C4, and D4. Essentially, choose the supplier's name from a dropdown list (B3), then decided what Quarter (C4) and the Year (D4). Once I have selected these 2 criteria, Excel will lookup on QTR4-testing for the corresponding data and automatically fill in Scorecard.

As it currently exists, with the formulas I have in B4, B6, B7, and most of column I. =VLOOKUP($B$3,'QTR4-testing'!$A1:$X130,7,FALSE) It ONLY references B3. But as I have populated more fake data for other quarters, my scorecard will need to be able to differentiate between a scorecard for Q1 and Q2 and so on.
 

Attachments

  • Sample Supplier Scorecard.xlsx
    55.6 KB · Views: 2
Hi ,

So what you are looking for is formulas in I10 through I31 , which take into account all the 3 selections in cells B3 , C4 and D4 ?

Narayan
 
I think I have solved it. What I did was create a concatenate/helper column on the scorecard AND the data set. My formula now references the helper column to return the values desired.
 
You must log in or register to reply here.
Back
Top