This is a VBA solution to the question. IMHO, this is more appropriate than a formula (in this case). The formula approach is OK, but there are drawbacks - you have to remember the CSE rule, and then copy the formulas down the right number of rows (which you don't know in advance), etc, etc.
This code uses the same assumptions as the formula approach.
1 - sheets = Stocks and Stock report
2 - Data in Sheets, columns A and B (header in row 1)
3 - lookup code is on Stock report
4 - Output is on Stock report
One of the advantages is that if new data is added to the Stocks sheet (i.e. the bottom row > 700), the vba automatically adjusts.
The code is self-documented. But the essence is that it creates an autofilter on "Stocks" using the lookup value as the criterion; copies the rows that meet the criteria; and pastes the result to an output range on "Stock reports". The output range is cleared before the copy/paste takes place so that there are no left-overs from any previous lookup.
I think there's something to be said for creating a dropdown list for the lookup cell. No doubt that could be automated too by getting the codes from Column A, getting the unique values, and then apply them to the lookup cell. Just a thought;)
Sub so_52537740()
' CREDITS
'
' Refer: https://stackoverflow.com/questions/17531128/copy-paste-calculate-visible-cells-from-one-column-of-a-filtered-table
' Date: 8 July 2013
' Submitted by: Jon Crowell (https://stackoverflow.com/users/138938/jon-crowell)
Dim src As Worksheet, tgt As Worksheet
Dim filterRange As Range, copyRange As Range
Dim lastRow As Long
Dim stocks As String, stockreport As String
' set values for sheet names
stocks = "Stocks"
stockreport = "Stock report"
' set values for Sheet variables
Set src = ThisWorkbook.Sheets(stocks)
Set tgt = ThisWorkbook.Sheets(stockreport)
' clear the exist target data
tgt.Range("A4:B" & Rows.Count).ClearContents
' turn off any autofilters that are already set
If src.AutoFilterMode Then src.AutoFilter.ShowAllData
' find the last row in the Stocks sheet with data in column A
lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row
' the range that we are auto-filtering (all columns)
Set filterRange = src.Range("A1:B" & lastRow)
' the range we want to copy (only columns we want to copy)
' in this case we are copying both columns A and B
' we set the range to start in row 2 to prevent copying the header
Set copyRange = src.Range("A2:B" & lastRow)
' filter range based on column A being equal the the value in Cell A1 of the stockreport
' consider making this a dropdown list so that there are no errors
filterRange.AutoFilter field:=1, Criteria1:=Format(Sheets(stockreport).Range("a1").Value)
' copy the visible cells to our target range
' note that you can easily find the last populated row on this sheet
' if you don't want to over-write your previous results
copyRange.SpecialCells(xlCellTypeVisible).copy tgt.Range("A4")
' turn off any autofilters that are already set
If src.AutoFilterMode Then src.AutoFilter.ShowAllData
End Sub
Giving due credit: There is, as they say, nothing new under the sun. I have based this answer on an excellent piece of work by Jon Crowell on a question in StackOverflow "Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table" in July 2013. Just goes to show what a bit of Googling and perseverance can achieve.
