VBA or Formula for lookup in Workbook the paste the first two characters of tab name

Dear Experts,

Please find here attached sample workbook, where i am expecting the result in the Master tab Cell B2 to match Cell C2 Value to search in column C for rest of the worksheets in the complete workbook and if the match finds then in Master tab Cell B2 paste the first two characters of the tab name in the MM/DD/YYYY format.

for eg. in the Master tab Cell C2, B is the customer name which is available in Mar 2018 funnel so the result in Cell B2 would be 03/31/2018.

Request to you please do provide the solution. Your valuable support would be appreciated.

Many thanks and regards,

Neilesh

Book2.xlsx

Thank you so much Dear Expert Glenn. Thanks a ton. You are Amazing. Dear Expert Glenn Is it possible to get the macro for Command Button so if do press command button then creates a list from cell L2 in the Master tab for all the worksheets names only which contains Month & Year in MMM YYYY format.

Once again sincerely appreciate your precious support.

Many thanks and regards,

Neilesh

That would help me dear expert.

Awesome and quite perfect Solution. Thank you so much Dear Expert for your valuable support. Thanks a ton Sir

Regards,

Neilesh

Dear Expert Glenn, I skipped one condition in get the expected Results. Apologies for the same.

Dear Expert, If i do have two same customer name one is appearing in Jan 2018 Funnel and the next same customer is also appearing in Feb 2018 Funnel and if i am repeating too in the Master tab so will it provide only for a tab or for all the different months tab.

Like in the provided solution attached workbook B is appearing twice. So one is in Jan 2018 funnel and the next is appearing in Feb 2018 Funnel but the result is giving 01/31/2018 in the both the cases.

Request to you, please do provide the solution for the same Sir.

Many thanks and kind regards,

Neilesh

Book2 (10) GK.xlsm

Sir we can keep as Month name and Year in the format of MMM-YYYY for column B in Master Data. It is not necessary to keep in Date format.

Sir, if the customer name is re-appearing in the different worksheet. I do expect only the different month name with the year to appear either in MMM-YYYY format or in MM/DD/YYYY format.

i guess you are absolutely right sir, but if the date is not updated in the month tab then i was thinking to get the output with the help of indirect formula. The reasons is that we extract the data from CRM on weekly basis. Sometimes in the month column it updates by the Sales team and sometimes not.

Dear Sir, Please find here attached workbook with provided Solution. Where few Customers are reappearing in different tabs but the output date or period showing only for the first tab. Like C is appearing in the all four tabs but i am repeating C in the master tab it is giving output only for the first tab.

Book2 (10) GK.xlsm

Sir as and when months will pass the tabs name will be added for a month on monthly basis.

Sir please find here attached Workbook filled with the date in the month column.

Book2 (10) GK.xlsm

Sir Maximum worksheets can be 52.

Ok sir we can minimize it upto 27.

Request to you please help me out Sir.

Which begs the question: why have 52 sheets when one will do (as each sheet has a date)???

Oh i am so sorry Dear Expert Glenn maximum would be 15 worksheets. Thank you so much Dear Expert John. 12 will be for twelve months and one would be the Master tab and first two tabs will be used as dashboard.

Once again i am very sorry Dear expert.

Dear Expert if we keep twelve tabs for twelve months then the year will be off from the tab name. it will be only "Jan Funnel" and in the month column B will be used as MM/DD/YYYY in each tab.

Dear Expert Glenn Sir, There will be the date in "MM/DD/YYYY" format in each tab's column B as i have provided in the last workbook and the tabs name will be named as "MMM Funnel". Like if the month is for Jan then it would be named as "Jan Funnel".

Dear Experts, can you please help me out.

Dear Experts, Can we try with including Small if, & Row formula in the present formula so if the Customer name matches in the list then it will search in the next tab.

Please Login or Register  to view this content.

Click RUN

NOTE: Current customer selection is placed in column C of MASTER and overwriiten by results. I recommend you place the Customer selection elsewhre in MAster (or other sheet). Minor change to VBA.

Dear Expert John, Thank you so much Sir, Exactly the same i was looking for Sir. Sir as you said the put the criteria in different place, so is it possible to make the changes in the provided script. As i have kept the criteria in the Master tab at cell N for the first column of every tab.

Adding to the present requirement, Sir is it possible to make the changes the script so if the customer name is getting repeat in the master name then the whole row upto the column H will be highlighted with different color.

please find here attached modified workbook. Once again many thanks and kind regards,

Neilesh

Sir i have tried to change the criteria from Customer to owner but the result is not giving the correct. Please find here attached modified workbook where i have changed the range from customer to owner but when i clicked to Run it showed only 3 line items Customer B but if feel it should be 5 line items.

Request to you kindly do provide the solution.

Many thanks and kind regards,

Neilesh

Sir i do expect based on Owner name and Named range of worksheets name criteria, when i do click run then the complete details for the selected owner will be pasted in the master data from the complete workbook and if the customer name is duplicate in the master tab then the particular row from cell A to H will be highlighted in different color.

I have tried to do so by putting the owner name in Cell A in the Master tab, in the immediate last attached workbook but it is not giving correct output.

Request to you please do help me out.

Many thanks and kind regards,

Neilesh

Please Login or Register  to view this content.

You Rocked John Sir. Thank you so much for saving me. Simply Awesome Sir. Thanks a ton Sir. Sir i do have a small concern if i do need to insert more columns in the master tab as well in the months tab so in that case can you please highlight the in the VBA script where i will be required to change.

Many thanks and kind regards,

Neilesh

I'll leave to work that out: it should be obvious from the comments in the code as you have to extend the range of "CustData".

or will it be fine sir if i keep open the thread by tomorrow so can i can get confirm from the Sales team that how many more columns would be inserted by them or this works for them so accordingly i will let you know Sir.

Truly apologies sir for bothering. Shall i confirm to you Sir with in an hour about the adding the columns so that i can get the final format from them. Only the columns would be added sir but the criteria would be fixed.

Sir i am not at all well versed with VBA so i will not able to upgrade in case if there is any changes. Request to kindly do help on this for the last time.

Once again apologies Sir.

Please post a final sample file

Dear Sir, Please find here attached the final format where the length of columns have been increased upto column AA. Dear Sir, Request to you kindly do upgrade the script as per current scenario.

Dear Sir, only few columns have been added but the criteria columns remains the same. Thank you so much Sir for your valuable support. Thanks a ton Sir.

Regards,

Neilesh

Please Login or Register  to view this content.

Variable "nCols" is column count

Thank you so much Dear Sir. Thanks a ton Sir. I do appreciate your valuable support and precious phase.

Many thanks and kind regards,

Neilesh

Please Login or Register  to view this content.

NOTE: if you have headings after column AA, then set ncols=27 (highlighted in above code)

Thank you soooooooooo much Respected Sir.

Respected Sir Please find here attached updated sample file

Please Login or Register  to view this content.