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 one sheet.  I created a column for each day of the month.  I have entered the formulas in Day 1 to pull data from the Day 1 worksheet.  I want to pull the same data into the other columns (1 column for each days worksheet). 

How can I copy the formula over without having to manually change it 30 times for each line of data??

For instance, I have "Cash on Hand" and in column 1 I have a formula "=-'1'!$J9  to pull data into that cell from worksheet 1 - cell J9.  However, if I copy that formula into columns for day 2, 3, etc. I find that I am having to manually change the "1" in that formula to a "2", then a "3" and it is awfully time consuming.  

Surely there is an easier way to copy the formula so it pull data from that cell for different worksheets??

I'm attaching a sample file so you can see more easily.  My formulas are correct in Column D for Day 1... but I need to pull data for days 2 - 31 as well.  Ugh! 

Would really appreciate your help!  I know it's got to be easier than manually changing the formulas 30 times for each line of my summary spreadsheet!  At least I hope so!! 

TIA!!

excel
Formulas and Functions

2 Replies

  • 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