Consolidated dynamic single view

Omega67

New Member
Joined
Apr 15, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
@TomUrtis Sir,

Guide me to get a consolidated view of all tabs in a excel work book.
Excel work book has multiple tabs, number of tabs may vary.
Each tab has data with same format and would start from same row and column across all tabs, however number of records in each tab may differ.
Records in each tab may be similar to records in other tabs or may be different.
One tab (CONVIEW) shall have a consolidated view of all selected tabs. Any change in any of the selected tab should automatically reflect in CONVIEW tab.

Eg:
Tab 1 has 4 records, Tab 2 has 10 records, Tab 3 has 30 records.
CONVIEW tab shall have 4 + 10 + 30 records.
If tab 1 is updated with 2 additional rows, then these two records should get reflected in CONVIEW tab as 4 + 2 + 10 + 30
Records in CONVIEW tab should automatically get sorted on any one column.

Additional tabs might be there in the worksheet, which might not be included for consolidated view.
Certain cells of CONVIEW may be linked to other tabs of the worksheet which are not part of consolidated view
 

Attachments

  • CONVIEW.jpg
    CONVIEW.jpg
    103.4 KB · Views: 2
  • TAB A.jpg
    TAB A.jpg
    99.7 KB · Views: 2
  • TAB B.jpg
    TAB B.jpg
    64.6 KB · Views: 2
  • TAB C.jpg
    TAB C.jpg
    74.8 KB · Views: 2
  • TAB RATES.jpg
    TAB RATES.jpg
    44.5 KB · Views: 2

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Omega67

New Member
Joined
Apr 15, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
UPDATED

@TomUrtis Sir,

Guide me to get a consolidated view of all tabs in a excel work book.
Excel work book has multiple tabs, number of tabs may vary.
Each tab has data with same format and would start from same row and column across all tabs, however number of records in each tab may differ.
Records in each tab may be similar to records in other tabs or may be different.
One tab (CONVIEW) shall have a consolidated view of SELECTED COLUMNS (uniform across all selected tabs) of all selected tabs (ALL ROWS ARE SELECTED).
Any change/update in any of the selected tab should automatically reflect in CONVIEW tab.
Records in CONVIEW tab should automatically get sorted on any one column.

Eg:
Tab A has 6 records, Tab B has 3 records, Tab C has 4 records.
CONVIEW tab shall have 6 + 3 + 4 records. Columns D,E,F,G,H selected in each of the tabs
If tab A is updated with 2 additional rows, then these two records should get reflected in CONVIEW tab as 6 + 2 + 3 + 4

Additional tabs might be there in the worksheet, which might not be included for consolidated view.
Certain cells of CONVIEW may be linked to other tabs of the worksheet which are not part of consolidated view
 

Attachments

  • CONVIEW.jpg
    CONVIEW.jpg
    103.4 KB · Views: 0
  • TAB A.jpg
    TAB A.jpg
    99.7 KB · Views: 0
  • TAB B.jpg
    TAB B.jpg
    64.6 KB · Views: 0
  • TAB C.jpg
    TAB C.jpg
    74.8 KB · Views: 0
  • TAB RATES.jpg
    TAB RATES.jpg
    44.5 KB · Views: 0

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,151
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe this, it will clear the CONVIEW sheet each time the workbook is opened and refresh with the lates data
VBA Code:
Sub workbook_open()
Dim lr As Long, ws As Worksheet
lr = Sheets("CONVIEW").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("CONVIEW").Cells.ClearContents
    For Each ws In Worksheets
        If ws.Name <> "TAB RATES" And ws.Name <> "CONVIEW" Then
            ws.UsedRange.Copy Sheets("CONVIEW").Range("A" & lr + 1)
            lr = Sheets("CONVIEW").Cells(Rows.Count, "A").End(xlUp).Row
        End If
    Next ws
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,735
Messages
5,638,057
Members
417,001
Latest member
MSteel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top