How to create a table in a separate tab of a financial spreadsheet

Hello Steve Backman,

Welcome to Microsoft Q&A Forum.

From your description, I understand that you want to build a table in a separate worksheet (tab) of an Excel financial workbook that auto‑populates/updates from the main data sheet without manually copying or pasting.

Here are some workarounds that you can kindly try:

1. Structured References (Tables) and Simple Formulas:
   • Convert the source range to a Table:
   • Select your data > Insert > Table > name it (e.g., tblData) via Table Design > Table Name.
   • On the target tab (e.g., Summary), use structured references to bring over fields: =``tblData[Amount] =``tblData[Category] =``tblData[Date]
   • If you need totals or KPIs, use SUMIFS, COUNTIFS, etc.: SUMIFS(tblData[Amount], tblData[Account], $A2, tblData[Date], ">=" & $B$1, tblData[Date], "<=" & $C$1)
   • Here, $A2 is the account selector, $B$1:$C$1 could be a date range.
2. Dynamic Arrays: FILTER, SORT, UNIQUE
   • Filtered view on the Summary tab (spills to as many rows as needed): =FILTER(tblData, (tblData[Account]=$B$1) * (tblData[Date]>= $B$2) * (tblData[Date]<= $B$3), "No rows")
   • Sorted & unique helpers: =``SORT(FILTER(tblData, tblData[Category]=$D$1)) =``UNIQUE(tblData[Account])
   • Top‑N by Amount (with SORTBY): =TAKE(SORTBY(FILTER(tblData, tblData[Category]=$D$1), tblData[Amount], -1), 10)
3. PivotTable on a Separate Tab:
   • Insert > PivotTable > Select tblData, place on Summary tab.
   • Drag Account/Category to Rows, Date to Columns (group by month/year), Amount to Values.
   • Use Report Filter (slicers/timelines) for interactive filtering.
4. Power Query:
   • Convert source to tblData.
   • Data > Get Data > From Table/Range > transform (filter rows, change types, add columns).
   • Close & Load To… > Table on Summary tab.
5. VBA:

      Sub CopyAccountRows()
          Dim src As ListObject, wsSrc As Worksheet, wsOut As Worksheet
          Dim acct As String, lastRow As Long
          Set wsSrc = ThisWorkbook.Worksheets("Data")
          Set wsOut = ThisWorkbook.Worksheets("Summary")
          Set src = wsSrc.ListObjects("tblData")
          acct = wsOut.Range("B1").Value  ' criteria cell
      
          wsOut.Range("A5").CurrentRegion.Clear
      
          Dim r As Range
          For Each r In src.DataBodyRange.Rows
              If r.Columns(src.ListColumns("Account").Index).Value = acct Then
                  lastRow = wsOut.Cells(wsOut.Rows.Count, "A").End(xlUp).Row + 1
                  wsOut.Cells(lastRow, "A").Resize(1, src.ListColumns.Count).Value = r.Value
              End If
          Next r
      End Sub
   

I hope my answer helps, please feel free to let me know if you need any further assistance.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Ian-T,

Here is a partial look at the tabs in my spreadsheet. There are many more including Jan to Dec, Net Worth, Month over Month and this bunch. The table only has 3 columns--Payee, Category and Account. You can get a partial view off the the right side.

What I want to do is have only one table. Each spreadsheet (and they are all identical) would draw the account and category from that table. That way I only have to update one table, not 15.

Is there a formula I could put in column J & K that would look at the table (let's say it's in Sheet 2) and give me the account and category based on the payee in column H? If so, how would I write it?

Thanks so much, Ian-T, for hanging in there with me. I'm just an old man trying to sort my way through technology newer than an abacus.

Ian-T

After a few fits and starts, I finally made it work.

I cannot thank you enough. I know it could be done easily. I am very certain that the original options you sent would also work. Unfortunately, they were way beyond my skill level.

I did have to adjust the J & K letters. But once data stated to fill the columns, that was pretty easy.

Thanks again. I am very pleased.