CODEWITHSUNDEEP
excelhyperlinkpivot-tablevba
user7144191
user7144191

Reputation:

EXCEL VBA - A Table with a Hyperlink or Pivot table?

I have a requirement wherein, I need to build a report from the data on the basis of date and reviews to it. This is the data that I have,

Sheet 1

Date    id Productsold   Reviews
2/1/17  1   A
2/1/17  2   B
3/1/17  3   C
3/1/17  2   B

On the Sheet 2, I have to build a table ( Pivot table or a normal table) automated on the basis of dates and number of products sold on that date. When the end user clicks on one of those numbers say 2 which is the number of items sold on 2/1/17. It has to open the sheet1 and show only the data on those date and add his review. Or it can open a new sheet with the data that belong to that date and allow him to add a review. But in this case, the reviews he added must reflect on the sheet1 as well.

what is the best option have I got here?

I Know Pivot table wouldn't allow me to save the information back to Sheet1.

Is hyperlink my only option?

Please kindly share your thoughts. It will help me automate them.

Upvotes: 0

Views: 162

Answers (1)

Bill Roberts
Bill Roberts

Reputation: 1171

I'd go with a table. Really starting to love them!

Anyway, I have a snippet of code for the filter of a table I'm working with: COLS.ListObjects("schema").Range.AutoFilter Field:=1, Criteria1:=Range("TABLE").Value

I called the table SCHEMA. I apply a filter. I specify the column, then set the criteria based on another range.

Somewhat working in your example, I might shoot for:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iSect As Range

    Set iSect = Application.Intersect(Sheets("sheet1").Range("B2:B4"), Target)

    If Not iSect Is Nothing Then
        COLS.ListObjects("schema").Range.AutoFilter Field:=1, Criteria1:=Target.Cells(1, 1).Value
    End If

End Sub

This code goes into Sheet1 since I think you mentioned someone clicking in sheet 1 triggers the filter on Sheet2.

COLS is the codename of the worksheet upon which the TABLE resides

I use the "cells(1,1)" just to make sure only one value is submitted to the filter.

Anyway, a very rough solution... I'd need to know more to assist better.

Upvotes: 0

Related Questions