Change Column Only, Not Row upon Copy Paste

[Michelle_L_MSU]

I'm hoping this community can give me a hand.

I need to copy a formula 49 times. However, the column needs to incrementally change while the row does not change. When I copy and paste, the column stays the same and does not change. Here is an example of what I'm trying to accomplish:

=Joe!B$30+David!B$28+Wataru!B$27+'Spectrum Lab'!B$33+Brad!B$26

=Joe!C$30+David!C$28+Wataru!C$27+'Spectrum Lab'!C$33+Brad!C$26

=Joe!D$30+David!D$28+Wataru!D$27+'Spectrum Lab'!D$33+Brad!D$26

Thank you,

Michelle

[#2]

@Michelle_L_MSU 

Do you want to fill or copy downwards? If so, let's assume that the first formula is in D2.

Change that formula to

=OFFSET(Joe!$B$30,ROW(D2)-ROW($D$2)+OFFSET(David!$B$28,,ROW(D2)-ROW($D$2)+OFFSET(Wataru!$B$27,,ROW(D2)-ROW($D$2)+OFFSET('Spectrum Lab'!$B$33,ROW(D2)-ROW($D$2)+OFFSET(Brad!$B$26,ROW(D2)-ROW($D$2)

You can fill or copy this down.

[#3]

Thank you @Hans Vogelaar for the speedy response. The formula actually goes in E2 and will go down. I am getting an error stating the formula is missing an opening or closing parenthesis.
A B C D E F
Year Date Funding Ceiling Funds Received Monthly Plan Cumulative Plan
1 9/23/2021 $3,574,967 $3,521,000 $0 $0
1 10/31/2021 $3,574,967 $3,521,000 $0 $0
1 11/30/2021 $3,574,967 $3,521,000 $7,207 $7,207

[#4]

@Michelle_L_MSU 

Sorry about that. It should be

=OFFSET(Joe!$B$30,ROW(D2)-ROW($D$2))+OFFSET(David!$B$28,,ROW(D2)-ROW($D$2))+OFFSET(Wataru!$B$27,,ROW(D2)-ROW($D$2))+OFFSET('Spectrum Lab'!$B$33,ROW(D2)-ROW($D$2))+OFFSET(Brad!$B$26,ROW(D2)-ROW($D$2))

[#5]

@Hans Vogelaar Thank you. I changed the D2 to E2. There error I see now is "You've entered too few arguments for this function.

[#6]

@Michelle_L_MSU 

Sorry, that's what you get when you try to write a formula without actually testing it.

=OFFSET(Joe!$B$30,ROW(E2)-ROW($E$2),0)+OFFSET(David!$B$28,,ROW(E2)-ROW($E$2),0)+OFFSET(Wataru!$B$27,,ROW(E2)-ROW($E$2),0)+OFFSET('Spectrum Lab'!$B$33,ROW(E2)-ROW($E$2),0)+OFFSET(Brad!$B$26,ROW(E2)-ROW($E$2),0)

Changing D2 to E2 is clearer, but not essential. The important part is the row number 2.

[#7]

@Hans Vogelaar Closer. I'm now getting the #REF! in the E2 cell and the entire F column. I really appreciate your help with this.

[#8]

@Michelle_L_MSU 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

[#9]

@Hans Vogelaar Thanks for offering. I'll pass at this time and appreciate what you have done thus far.

[#10]

@Michelle_L_MSU 

Usually copy/fill is simply a cheap and cheerful way of scanning the terms of an array/list without any formal recognition that such data structures exist.  If you can build the output table as an array, e.g.

= joe+david+Wataru+spectrumLab+brad

where the defined names apply to rows of the data sheets, and could be the result of XLOOKUPs, then a simple TRANSPOSE,

= TRANSPOSE(joe+david+Wataru+spectrumLab+brad)

will switch the rows to columns in the output table, without needing to resort to black magic.

[#11]

@Peter Bartholomew Thank you for the reply. I certainly did not realize changing columns only when pasting was such an advanced function. I appreciate your suggestions; however, I resorted to changing the columns manually.