Forum Discussion

Copper Contributor

May 20, 2022

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

excel

Formulas and Functions

HansVogelaar

MVP

May 20, 2022

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.

Michelle_L_MSU

Copper Contributor

May 20, 2022

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

HansVogelaar
MVP
May 20, 2022
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))
- Michelle_L_MSU
  Copper Contributor
  May 20, 2022
  HansVogelaar Thank you. I changed the D2 to E2. There error I see now is "You've entered too few arguments for this function.
  - HansVogelaar
    MVP
    May 20, 2022
    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.