Data validation and automatically Vlookup with match data

Hello,

i need a help in Vba by selecting two drop down list of data validation and automatically match data from Sheet (Data) to show matched datas on sheet (values).

i have two sheet the sheet (values) is output by selecting DV & vlookup and other sheet (Data) for input sheet to exact match with sheet (values).

Eg:- if i select first Data validation on TagNo : Red and selecting second main Tag no data validation of 10-RED-AA or 10-RED-BB then automatically other cells data to fill with matched data to display it.

i attached the sample worksheet. please help me to solve it.

if possible both by formula and vba shall i get it will be more appreciated because i am trying to learn Vba.

Hi mrice,

Thanks for the reply i inserted the code in sheet2 its works in 'I9' range only but if i select the Tagno: RED means the second data validation in Main tag no should display only red group only others colors not to show on the drop down list.

your code then i have to write each cell reference to get output is it easy way to short code to do vlookup please sir..

HI Dflak,

Excellent and its works perfectly what i needs and thanks for explaining the steps to create the pivot table and name mangers about offset ranges. i learned new things from you sir.

sir possible to help me also few things on this workbook.

1. The data which i input in sheet(values) by monthly bases so i need to create the coming month and year by manual its takes me lots of time. There is way to create automatically by next months when its reaches of 3rd week of every month.

Eg:- Now May 31 2018 i creates the column when Today date reaches of 26th may 2018 like 3rd week of month then create automatically columns of June month 2018 like continues..

2. i need to hide the formula and sheet password protection in sheet(values) except the two columns of table which is colored Red accent ( Sub Tag no & Pre sub Tagno) this is are the input values in sheet remains all other columns should be protected so others by mistakenly not to delete the formula and values.

Sir please help me on this 2 steps i will be grateful to you.

Originally Posted by dflak

Let me rephrase to see if I understand the requirement. The sheet that is called Values should really be called May-18. During the third week of this month, you want to create as new worksheet called Jun-18. I will probably make the code do this the first time the workbook is opened on or after the 21st of the month.

The Data sheet will remain the same with the exception of changes in content in the database. You can change / add / delete data to the table as long as you don't change the column names or order.

I think this may be easy to do.

I need a bit of clarification on the second requirement. You want to "lock down" everything above row 15 except Cell D2 and Cell E6 (so users can make the selections) What do you want locked down on the remaining rows?

Hi sir,

sorry for the late reply i went to office.

Let me rephrase to see if I understand the requirement. The sheet that is called Values should really be called May-18. During the third week of this month, you want to create as new worksheet called Jun-18. I will probably make the code do this the first time the workbook is opened on or after the 21st of the month. ?

yes Sir i need to create the month of every 3rd week of month but not separate worksheet i need it in same worksheet to create the next month like attached picture of 1st requirement.


The Data sheet will remain the same with the exception of changes in content in the database. You can change / add / delete data to the table as long as you don't change the column names or order. ?

Got it sir.. i add the final three additional column for my requirement and i hope it will not change in future.

I need a bit of clarification on the second requirement. You want to "lock down" everything above row 15 except Cell D2 and Cell E6 (so users can make the selections) What do you want locked down on the remaining rows?


yes i need to lock down some of the column and cells which i mentioned in picture of 2nd requirement.png file which is
All RED arrow need to lock the cell
All Green arrow need to allow the user to edit
All cell which is contains formula to hide and lock.

Originally Posted by dflak

I have a second question related to the second requirement:

Obviously the rows are set up to add data every day. What do you want to happen when a user changes the Tag No or Main Tag No? There is no "memory;" the program doesn't "remember" what was selected on any particular day.

Sir, you are right sir when user changes the Tagno & Main Tag no actually the project is all are set of each groups separately to add data every day. i dint know much excel idea's how to make all the data to save in each group separately and add to memory has single worksheet and also i need to execute the report by monthly base to save as PDF.

Sir if you can give me your best solution to possible ways to create the data by each group separately and execute the Sheet called (Report) to save as PDF.

Originally Posted by dflak

I understand the lockdown and how you want to add dates. It will help me slightly if you can full in the headers in row 13. I may use this information to set up a database.

I see that you want Sub Tab Number and Pre Sub Tab number editable by the user. At the moment, the data shows a 1 to 1 relationship with the main tag number. Are you telling me that you may have multiple sub tabs for a main tab and multiple pre sub tabs per sub tab? If so, please provide an amended sheet with this expanded for at least one of the colors. I will also assume you want the whole of columns C and F as drop-down if this is the case.

Are you telling me that you need one report for each Main Tag Number?

If this is the case, I suggest the following: behind the scenes will be a database sheet containing all the data in "normalized" format. I will take care of this. My assumption is that the combination of the date and the main tag number is what will make a record unique. It will eventually turn into a big database.

The user will launch a form to select the Tag Number and Main Tag Number and click a button on this form. It will bring up a worksheet with previous data filled in.

The user clicks a button on the sheet to commit changes in the data to the database. I'll put some checks in place so if the user leaves the page or closes the book, a prompt to commit will be displayed.

This database can be used to run the report. I assume that what you want when you fill in the year and month is to show only dates for that year and month.

Some of the pieces for this are in place already. Others will have to be built.

I will have to evaluate this as far as the level of effort is concerned.

Hi sir,

very sorry for the reply due to sick for three days...

your idea is awesome and you make me heads up.. as you said for data storage i have created in sheet ( values) from column AA of each separate group to store data on respective groups when user to enter the values to paste on it. so after we can take this unique data to grab the report sheet to execute as a PDF and Print. it just my idea sir.. if you have better idea on each group it will be more appreciate.

I understand the lockdown and how you want to add dates. It will help me slightly if you can full in the headers in row 13. I may use this information to set up a database.
i filled the headers as you said and also user enter only the data in column c & F only remaining columns there is some calculation to fill the result values on each column.
I see that you want Sub Tab Number and Pre Sub Tab number editable by the user. At the moment, the data shows a 1 to 1 relationship with the main tag number. Are you telling me that you may have multiple sub tabs for a main tab and multiple pre sub tabs per sub tab? If so, please provide an amended sheet with this expanded for at least one of the colors. I will also assume you want the whole of columns C and F as drop-down if this is the case.
As you said i crete the amended sheet the one of the colours it is possible to store the data or need to create each worksheet for each group of colors i need to make it in single worksheet for all color groups.
Are you telling me that you need one report for each Main Tag Number?
yes correct sir.
If this is the case, I suggest the following: behind the scenes will be a database sheet containing all the data in "normalized" format. I will take care of this. My assumption is that the combination of the date and the main tag number is what will make a record unique. It will eventually turn into a big database.
yes sir it will turn to big database only but i need to make more comfortable for user to enter the data. please help me on this sheet.
The user will launch a form to select the Tag Number and Main Tag Number and click a button on this form. It will bring up a worksheet with previous data filled in.
yes correct.
The user clicks a button on the sheet to commit changes in the data to the database. I'll put some checks in place so if the user leaves the page or closes the book, a prompt to commit will be displayed.
nice idea
This database can be used to run the report. I assume that what you want when you fill in the year and month is to show only dates for that year and month.
correct
Some of the pieces for this are in place already. Others will have to be built.
I will have to evaluate this as far as the level of effort is concerned
Thanks for you idea and your effort for me please do the needful to complete the project sir.

Hi sir,

In sheet (Values) more groups of columns which i created for purpose you asked to store the data should be somewhere for the reason i created the column but if you have any idea to store the data without which i created the duplicate groups of columns.
EX :- After the user enter the values by selecting the dropdown list of groups then values to store somewhere or better to keep separate worksheet for each group to store the data.
some of calculation also i attached please check it sir..

I attached with two sample workbook for example which one we can choose better option.

1. Tag N0 ver file is for single worksheet with group of values to enter within the sheet to store the data of groups.
2. Tag No ver -copy file is for each group i created the seprate worksheet to store the data. but only user can use and edit on the worksheet name called user sheet(values ).

I attached with two sample workbook for example which one we can choose better option.

2. Tag No ver -copy file is for each group i created the seprate worksheet to store the data. but only user can use and edit on the worksheet name called user sheet(values ).

second file

Hi sir,

Please at least solve that i need to protected the cell and automatically generate the month if less than 3rd week to create next month to solve the thread.

I'm still not completely certain what you are trying to do. You select the tag number in cell d2 and this auto-populates the cells indicated on row two of the data tab. What is the expectation for columns AA and later or is that just more examples?
Yes sir, when i first select the group of colors (Tag No) on cell d2 and second i will select the cell E6 (Main tag No which is only having the Unique ID ) then remaining has to be auto populates on row of the data tab. then user wants to enter the values for which is related the value of Main Tag no in C&F column.

The column AA as you said right sir, its just for the example that user enter the values by selecting the cell Main tag no (Ex:- Blue (Tag No) ----> 10-Blue-AA (Main Tag No)) and values store on only on main Tag no (10-Blue-AA) on may 1st and same day user will select also other group of colors Tag No (Green) and Main Tag no ( 10-Green-AA or 10-Green-BB or 10-Green-CC) and enter values for main Tag no and its store the value for only on Green Group of main tag no.

I will assume that AA and later are examples.
Yes correct.
How many VALUES sheets do you want?
18 no's of sheet values which is each main Tag no (unique id)
The program cannot display more than one Main Tag Number on the page. It does not maintain a "history" so if you enter 150 in cell C15 on May 1st and then change the Main Tag number and make another entry on May 2nd, there is no way that the program knows that previous entries were made under a different tag number.
yes sir your logic is correct the values should be on different for different main tag no only like same day i will the different values of 18 nos of main tag no on 1st of may and next day i will enter the another values on different main tag no like continues..

I can make it so that once you select a main tag number, it locks down everything except those columns you want to let the user update. You will not be able to enter in a new Tag number or Main Tag Number. Essentially, this means that you have one book per main tag number.
That good idea too but i need on one workbook same on different worksheet is possible to do it sir .. and grab the values to report sheet of selecting month & year to take printout or PDF.

Hi sir,

Thank you so much for your effort with brilliant ideas and thank a lot very happy to seeing that almost you reached to solve my problems.

1. The Pivot Sheet is where I do some of my "helper" calculations ?

Nice so all the calculation of values will store in pivot sheet and pivot sheet only i can access and other user to hide the sheet.

2. The Pivot Sheet also contains a Create Sheets Button. This button will create a sheet for each Main Tag item. It will not create a sheet if the sheet already exists. You will get a warning that the sheet exists, but the program will go on. So when you fill the data table with new items, run this program. You will have to remove sheets you do not want manually. You can hide the pivot sheet when you are done with it.

How to run the program:
- Go to the main sheet.
- Select a Tag Number and Main Tag Number
- Click on the Data Entry Button

The Data Entry button takes you to the appropriate sheet for data entry. ?

ok got it so same duplicate sheet it will create it excellent.

3.On each data entry sheet, Columns C and F are open for data entry. All other cells are locked. ?

still i think except C&F all others are not locked yet. may be in next version i can see it. :-)

4.If, when you open the sheet, the last date on the sheet within 7 days of the next month, the next month's worth of dates are generated. ?

Hmm.. correct sir that's i need it. i will check it on Friday when i open the file.

5.There is a "Main" Button in Cell A1 to take you back to the main page. ?

Super hyperlink to worksheet.

6.To run a report:
- Select the Tag Number and Main Tag Number
- Use the drop-down lists in cells B8 and B10 to select the Year and Month.
- Click the make report button.?

Wow.. good idea but when i click the report button its showing error to me for the "slicer sheet" Picture attached.

7.The make report button takes you to the filled in report sheet. ?

still report sheet i dint to display the values on report sheet and other values also like Date of change tag & date of inspection Etc.. this values from the "data sheet" to fill on the report sheet to take print or PDF.

8.The print button prints the report to the default printer.?

yes it store the PDF working but for printer i need to see the print preview dialog box and i can select the printer to take print out is better for me because of many printer on the server.

9.The PDF button makes a PDF file in the directory with the spreadsheet with the same name as the spreadsheet except for the PDF extension. /

worked fine thanks..

10.I did some QA on this. I suggest you play with it for a while.This Friday, when you open the data entry sheets, June dates should be appended. ?

ok so when i open on this 27th Friday the date for june month will create automatically correct.

11. I have a bug with getting the years and the months for drop down for the reports. At the moment, I have to know what months are available before I can filter for the month. I will continue to work on this. In the meantime, continue to play with this report. ?

I have the data from January 2018 so i can make report on from 1st of January 2018 onward.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

A) when i click on the cell the formula are to hide is and enter should be on C & F column only remaining other columns are cell has to locked.

B) what is Report Raw sheet works for ?

C) when i select the Tag No and second main tag no to auto select to show like if i select Blue then dint wait for to select the 10-Green-AA to 10-Blue-AA on Main tag no.

Originally Posted by dflak

I will look into your comments and concerns tomorrow.

The Raw Report sheet can be hidden. I copy in and filter the report you want to see here - then I copy the pieces to the final report.

As for the cells not locking. That might be a temporary thing. The code that adds the date locks all the cells on a sheet and then unlocks the ones the user can use for data entry. I think I can fix this so it happens on sheet creation.

I can do the print preview. That should be fairly simple.

I don't know why the slicer is not working for you. Also I don't know why you are not getting cascading drop-down lists. I'll look into this.

Sir,

Yesterday i opened the file in office the silencer and drop down is not worked and am using at office excel-2010 version and today i opened file in my home PC using excel 2013 version the silencer is working but cascading drop down list still not working properly. i think silencer will not work in Excel 2010 version.

As you said remaining stuffs am waiting to see it i hope it will work on next version file.

Originally Posted by dflak

I cannot duplicate the issue with the cascading drop-downs. I suspect one of several things might be wrong:
- You may not have macros enabled.
- If macros are enabled, but you have a VB error in *any* macro, then none of the macros will work. Close the debugger and the cascading drop-downs should work again.
- This may also be at the root of the cells not locking too. However, I still have more testing to do in that area.

You are correct. Slicers do not work with Excel Tables in Excel 2010; that feature came along in 2013. I will have to come up with a different approach to making the report. I will probably use pivot tables and set the filters.

Hi sir,

i was enabled the macro in both excel 2010 & 2013 version at my office the cascading drop down not worked and slicers too but in excel 2013 i tested again both worked drop down and slicers.

Now issues is both Excel version the cell locking is not working and mostly i will use this file in company PC only so all the Vba code and Formula need to work on excel 2010 version is most useful for me sir.. i am sure that you will solve my issues. waiting for you thanks a lot for your Effort.

HI sir,

i will check out the file and play it and give you feedback soon sir..

Originally Posted by dflak

Here is a version that works without slicers. I also think I fixed the issue with the sheets not locking. About the only minor bug is that when you look at the report on the screen, you may have an additional blank line (and in February several blank lines). These lines do not print nor do they show up on the PDF.

Hi sir,

Sorry for late reply i was out of place for few days. i fully tested your file in both my company and Home PC its working perfectly thanks a lot for your effort and Awesome created for my project.

Sir i have only few suggestion could you please solve it.

1. As i said i need to hide all the formula's in sheet.

2. Hide all the sheet Except MAIN SHEET, REPORT SHEET, DATA SHEET. remaining through the link i need to use it.

3. In all group sheet few columns need to have access unlock the sheet for Data entry which is i mentioned in picture.

4.In 10-BLUE-AA sheet the green color columns i have some formulas its needs to auto copy when months creating time to all group sheets.

Please sir could you help to solve this also it would be great help for me.

Hi sir,

Thanks for the reply

1. I can hide the formulas, but I need to know what they are. I don't have any of the sheets populated with the formulas. This can be set up on the Template sheet.

The formula which is belongs to check the corrected Delta pressure for the Equipment's which is healthy or not so user will enter the two data of pressure of the equipment and current flow of Equipment manually take from DCS/SCADA and enter into column C & F and then remain other column will calculate by formula the Differential pressure for the Equipment is still healthy or bad condition.

The all formula will be there in column of D,E,G,H,I the sample formula i mentioned in sheet of 10-BLUE-AA you will find it the same formula will be all other Main Tag No Sheet which we created the 18 sheets.

i attached the file with formula in sheet name of 10-BLUE-AA for sample same to be done in all other sheets automatically when creates the months.

The remaining the white columns of J,K,L,M,N are whenever maintenance did the Preventive maintenance of Equipment jobs done we will be enter the Date of job done. That latest date will be appear in cell D9 & D10 , E9 & E10. it should change auto fill when enter the dates in white columns. from the date of change and inspection the validity will be calculate of 1 year for expiry date remaining which is in cell D11 & E11.

2. I can hide all the sheets except the ones you mention. This means you must unhide any of the sheets on which you want to do data entry manually.

which sheet cannot hide sir, yes i will select the sheet in Main sheet that i want to enter the data entry manually by daily basis and again go back to main sheet and select next one.

The other sheet like Template , Report Raw, Pivot sheet should not misuse by other user to change on it. only i have to access on this 3 sheets same like other above cell the formula other user should not change on it or by mistakenly not to delete the formula for safety protection.

3. This relates to point 2. What do want? I can give you another button that will list the sheets and let you select the one you want to unhide.

yes may be this is one of the best solution too sir, but this 3 worksheet name - Template , Report Raw, Pivot sheet should be very hidden and i have to only access to change on it remaining sheet for other user to enter the data's.

4. See point 1 - what formulas do you want?

i attached the file and you can see the formula in worksheet - 10-BLUE-AA you can check those formula's even i will be very happy it can be done by VBA for formula it will be most great help for me to most safety protection of my file.

Hi Dflak sir,

Any update please...waiting for your reply.