Forum Discussion
Change Column Only, Not Row upon Copy Paste
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
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.
PeterBartholomew1 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.
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.
- Thank you HansVogelaar 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,207Sorry 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))
