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 st...
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,207
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
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))
HansVogelaar Thank you. I changed the D2 to E2. There error I see now is "You've entered too few arguments for this function.
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.
- HansVogelaar Closer. I'm now getting the #REF! in the E2 cell and the entire F column. I really appreciate your help with this.