Posts by rehan kazi

In fact, dealing with columns AQ to AY is the first priority in worksheet Display.

However, it would also be great if you modified the previous macro or

added some extra lines of code to increase its speed and performance.

Thanks in Advance....

The whole idea is…..with a single click…. match multiple offset cell criteria's into multiple sheets….filter the rows, if criteria matched…..extract filtered data from multiple sheets….. paste (only values...no formatting) them into a single sheet.

Hi....

By Click on Summary sheet cell C8 or D8, the following action will take place.

First action: Match Summary sheet cell criteria C8, D8 & E8 with Lab Report Sheet column C, D & E. If matches, filter the match columns C, D&E, copy the filter data from specific columns (O6:AN column), paste them into the Display sheet D9:AD column.

Second Action: Match Summary sheet cell criteria C8, D8 & E8 with Retrieval Sheet column C, D & E. If matches, filter the match columns C, D&E, copy the filter data from specific columns M6: M & Q6: Q columns (If columns C, D & E having duplicate criteria’s, then copy maximum value from Q6: Q) and paste them into the display sheet AE9: AF column, according to the dates in the Display sheet column D.

Third Action: Match Summary sheet cell criteria C8, I8 & I6 and Display sheet column D dates with JCB Sheet row1,2 &3 and column B. If matches, filter the match column, copy the filter data from specific columns where below formula is true and paste them into the display sheet AQ9: AS column, according to the dates in the Display sheet column D.

The following formula in the Display sheet column AQ,AR & AS, gives perfect output, need to be replaced with VBA code

(formula for column AQ) INDEX(JCB!$C$6:$CB$500,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Summary!$C$8&Summary!$I$8&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0))

(Formula for column AR) OFFSET(JCB!$B$5,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Summary!$C$8&Summary!$I$8&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0)+1,1,1)

(Formula for column AS) OFFSET(JCB!$B$5,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Summary!$C$8&Summary!$I$8&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0)+2,1,1)

Forth & Fifth Action: Will work like the fourth action, only the criteria cell of the summary sheet will be different

For Forth Action, the Summary sheet cell criteria will be C8, K8 & K6 and output will come in Display sheet column AT, AU & AV

For Fifth Action, the Summary sheet cell criteria will be C8, M8 & M6 and output will come in Display sheet column AW, AX & AY

In attached sheet, placed the formulas in display sheets cell AQ9:AY13 , which gives the exact result ....what I am looking for

Sixth Action: For Con sheet I guess no further explanation is required as you have already prepared the code for it .....in the module tab.....If so, I will explain...

I hope you will get my explanation.

.......Will try

Feel sorry for the inconvenience ....respect your efforts and very grateful to you.

Let me work behind it and will back to you....

I think you're asking for full example....I don't have the system now. I'll explain you as soon as I get it

Hello Carim...

I can understand,should not do this while accepting experts advice....I'm compelled as this is the users requirement and I didn't aware with other method to bring the same output. However,I have given compleat example in Summary,JCB & Display sheets in the attached file above.

If you need further explanation I will be happy to provide.

Hi..

Do you recommend to run the sheet code by clicking and the module code via the button or do you have any other recommendations?

My point is only that output should be the same now what I am getting.

You are absolutely right, I am 100% satisfied with your words. I'm clean in coding. I have no other choice. Just surfing and gathering code from many sources and trying to achieve my goal. By clicking on the cell, extract the required data from several sheets and paste them into one sheet.

......

Hi Carim

Yes, the final product will be the same. but that criteria's was looking in one direction and this will looking in two directions.

I tried hard and failed to get it in two directions.

Hi,

Seeking help in a creation of VBA code for Two dimensional lookup with multiple criteria.

Three criteria's are in the summary sheet and one in the Display sheet in column D (variable).

If all four criteria's matched in a JCB sheet than VBA will extract the data and paste in to the Display sheet AQ to AY column until there is no more criteria match in Display sheet column D.

Perfect....Thanks a lot

Hi

Output result is perfect......Now the problem is, if click on an any empty cell,the macro will run

...in fact it should only run when click on the target range which is columns D and E.

Yeah, that's exactly what I was looking for.....

Thanks

Hi roy UK

I need more help .... How can I combine the above code prepared by you with the below code ?

i tried alot......but receiving else if without if error...

                 ElseIf Not Intersect(Target, Range("E7:E130")) Is Nothing Or .Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
 
                Sheets("Display").Range("I3").Value = .Offset(, -2).Value
                Sheets("Display").Range("K3").Value = .Value
                Sheets("Display").Range("Q3").Value = .Offset(, -1).Value

 
                        If Not .Offset(0, 4) = Empty Then
                        Sheets("Display").Range("AG4").Value = .Offset(0, 4).Value
                        Sheets("Display").Range("AG5").Value = "North"
                        End If
                                                
                        If Not .Offset(0, 6) = Empty Then
                        Sheets("Display").Range("AJ4").Value = .Offset(0, 6).Value
                        Sheets("Display").Range("AJ5").Value = "South"
                        End If
 
                        If Not .Offset(0, 8) = Empty Then
                        Sheets("Display").Range("AM4").Value = .Offset(0, 8).Value
                        Sheets("Display").Range("AM5").Value = "East"
                        End If
 
                        If Not .Offset(0, 11) = Empty Then
                        Sheets("Display").Range("AP4").Value = .Offset(0, 11).Value
                        Sheets("Display").Range("AP5").Value = "West"
                        End If

Thank you roy UK

Yes, just now checked. .....It was my fault....added additional code..

If you click on the target cell, it will work perfectly. If you click on a cell other than the target range and then click on the target range, VBA will stop working for the target range

Hi royUK....

That's what I was looking for…... Working perfectly , however there is a single issue, If click on another cell instead and return to the target range, the target range will stop firing...... will need to reset Selection change each time.

Thanks a lot for your effort

Looks like it will work. As soon as I get access to my original file. Will check and back to you.....