PDA

View Full Version : VLOOKUP or INDEX NATCH



austenr
09-05-2015, 03:19 PM
14329

On my Input Log sheet above there is a column called Completed Date for each Supplier for Scorecard, Audit and BRM. What I need to do is take that date and put a "C" on the Master Schedule sheet for completed in the appropriate cell for the date and Supplier.

Master Schedule sheet:

14330

Sorry for the snippet. Workbook is too large to upload.

p45cal
09-06-2015, 09:41 AM
Formula or macro?
It would help an awful lot if you could supply a workbook with reduced data, just containing those two sheets, where there were at least some rows in both that corresponded.
I'm thinking for a formula solution that a formula in the cells in the Master Schedule sheet that would check for the existence of the company in column B on the other sheet and whether there was a completed date and whether that date was the same as in row 1 directly above the formula in the Master Schedule sheet. Something along the lines of
if(vlookup($B2,'Input Log'!$B$2:$F$1000,5,false)=C$1,"C","")
entered in C2 and copied across and down.

austenr
09-07-2015, 08:58 AM
Thanks, that didn't work but maybe its because THE VALUE OF column B of Input Log is the result of a VLOOKUP?

I tried something like this but that didnt work either:

=vlookup(vlookup($B2,'Input Log'!$B$2:$F$1000,5,false),=c$1,”C”,””)

p45cal
09-07-2015, 01:51 PM
It becomes pure guesswork without a workbook to try on.

austenr
09-07-2015, 02:26 PM
I put the workbook in my dropbox account.

Here is the link:

https://www.dropbox.com/s/i9gbjvlvd0e6hoc/2015_SRM_Schedule_Template.xlsm?dl=0

p45cal
09-08-2015, 02:43 AM
Two problems.
1.Although the date in column F of the Input Log looks as if it is just month year, it isa full date, and not just the first of the month; when comparing these dates with the dates on the first row of Master Schedule you rarely get a match because those date are the first of each month. This should't be difficult to work around.
2. There are multiple entries on the Input Log sheet with different types of Action in column D (even multiple entries with the same Type of Action). Which one to take?

austenr
09-08-2015, 05:55 AM
I would go with #1.

p45cal
09-08-2015, 09:11 AM
I would go with #1.lol!
These 2 problems are not mutually exclusive; there are 2 problems and both need to be addressed.

austenr
09-08-2015, 01:17 PM
Change in requirements. Now using Summary sheet and Master schedule. Still cant get it to work though.

p45cal
09-09-2015, 03:20 PM
Test the following:
Master Schedule sheet:
in C2:
=IF(EOMONTH(VLOOKUP($B2,'Summary-Schedule'!$B$2:$K$999,8,FALSE),-1)+1=C$1,"C","")

in C3:
=IF(EOMONTH(VLOOKUP($B3,'Summary-Schedule'!$B$2:$K$999,10,FALSE),-1)+1=C$1,"C","")

in C4:
=IF(EOMONTH(VLOOKUP($B4,'Summary-Schedule'!$B$2:$K$999,6,FALSE),-1)+1=C$1,"C","")

Select the three above cells together and copy across, then the three rows together and copy down.

There's not much to see especially as row 1 jumps from Dec-2014 to Jun-2016 at columns L:M where one of the results would show.

austenr
09-10-2015, 12:35 PM
Thanks. That solved it. After I fixed the dates in the top row Im right where I need to be.

austenr
09-11-2015, 06:49 AM
One question I have though. If the date on the Input Log changes, is there a way to move the "X" to a different date that is chosen and remove the "X" from the original spot?

austenr
09-11-2015, 07:19 AM
Never mind it already does that. :hi: