Forum Discussion

danazee's avatar
danazee
Copper Contributor
Aug 24, 2024

Copying a Formula in a workseet to pull data from other worksheets

Hello!  I need some help!  I have a file that has a worksheet for each day of the month.  I also have a sheet that I want to pull key data from each worksheet to create a financial snapshot all in...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 25, 2024

danazee 

If you can access Excel 365 radically different approaches are possible.  The starting point is to define 3D ranges for the credits and debits.

credit
='1:3'!$J$9:$J$24

debit
='1:3'!$I$9:$I$24

The new functions HSTACK and VSTACK work with 3D ranges to build stacked arrays

=LET(
    credits,   HSTACK(credit),
    debits,    HSTACK(debit),
    combined,  debits - credits,
    recordNum, XMATCH(SummaryItems, description),
    CHOOSEROWS(combined, recordNum)
)

The XMATCH and CHOOSEROWS are just an initial attempt to rearranging the records to match your output order. 

 

Resources