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

[danazee]

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!!

[#2]

@danazee 

=INDIRECT(ADDRESS(10,9,,,E1))

This formula returns the intended result in my sample file.

[#3]

@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.