Linking Excel filter Run Ids from user selection tab

[Abhishek Pratap Singh]

Hi Friends,

Need your help to solve this,

I want to link Run id (Column H) on East Series tab (sample file), with the user selection tab from where user can select from three different run id's (Series 1, 2 or 3) and it will be automatically selected in the Run id of East series tab and from there all the values get pulled from the East Series tab into output tab, should depend on the user selection.

For e.g. by selecting "series2" in user selection tab, it will select "series2" in the run id of East Series tab and subsequently the values of series2 will be shown. And the values selected will be shown in output tab.

Also, please do not use macros.

Regards
Abhishek

 

[p45cal]

if you right-click on the User Selection tab and choose View Code, then paste the following code where the text cursor is flashing:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$12" Then
  Sheets("East Series").Range("D4").AutoFilter Field:=5, Criteria1:=Target.Value
  Sheets("Output").Range("D5").CurrentRegion.Clear
  Sheets("East Series").AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy Sheets("Output").Range("D5")
End If
End Sub

Then close the newly opened window.
Now try selecting a series in F12.

I've attached a file where this works but I've also added a pivot table in the Output sheet, this is updated automatically as is the table above it, but it can also be used independently.

Any use?

 

[Abhishek Pratap Singh]

if you right-click on the User Selection tab and choose View Code, then paste the following code where the text cursor is flashing:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$12" Then
  Sheets("East Series").Range("D4").AutoFilter Field:=5, Criteria1:=Target.Value
  Sheets("Output").Range("D5").CurrentRegion.Clear
  Sheets("East Series").AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy Sheets("Output").Range("D5")
End If
End Sub

Then close the newly opened window.
Now try selecting a series in F12.

I've attached a file where this works but I've also added a pivot table in the Output sheet, this is updated automatically as is the table above it, but it can also be used independently.

Any use?

Hi,

Thank you very much it solved my problem.

Thanks a lot.

 

[Abhishek Pratap Singh]

Hi,

Can you help me with this also?

In the attached file i m trying to identify the largest owner and holding company (columns E and F) for every "Plant ID". In the excel file you’ll notice that each unit’s nameplate capacity refers to the owner’s share. You can therefore find the largest owner(s) using nameplate capacity.

Now, I'm able to find the largest owner and holding company name in column L&M in yellow by using Maxif, index and match function. But here are multiple largest owners with the same share (e.g., 50%/50% or 40%/40%) so I want to generate multiple owner name and multiple holding company name (with max. capacity & having same share) in alphabetical order separated by a “/” in the same cell.

for e.g. Owner name for plant id B405 = gg/guru/manoj and
holding company name = kohli/lala/manohar

Also, while doing that consider column "I" which shows unit no. so, one plant ID is having different units so, we have to take them separately.

Thanks & Regards,
Abhishek

 

[Marc L]

Last post is a duplicate of a new thread !

This thread is now closed …

​