Microsoft Office Forums need help with formula when dragging down
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
03-05-2019, 02:55 PM
 JVenz0913 Windows 10 Office 2019 Novice Join Date: Mar 2019 Posts: 2
need help with formula when dragging down

I want to have the cell reference change when I copy it down I am working out of Excel Office 365

This is the formula in the cell
=SUM('Annual Invoice Breakdown'!D35)

The next formula I want to read
=SUM('Annual Invoice Breakdown'!E3:E5)

Thank you kindly!
#2
03-05-2019, 03:14 PM
 p45cal Windows 10 Office 2016 Expert Join Date: Apr 2014 Posts: 280

you could try something along the lines of:
=SUM(INDEX(\$D\$3:\$Z\$5,0,ROW()-13))
but the 13 in there is because the topmost cell my formula was in was on row 14.
You need to adjust the 13 to the row the topmost formula is in minus 1, then copy down.
#3
03-06-2019, 07:46 AM
 JVenz0913 Windows 10 Office 2019 Novice Join Date: Mar 2019 Posts: 2
Didn't work

The data I am trying to pull is on another sheet within the same book. I just need the Letter of the column to change once i copy and paste the formula down.
#4
03-06-2019, 08:13 AM
 bosco_yip Windows 10 Office 2016 Novice Join Date: Feb 2019 Location: Singapore Posts: 7

Try….........................

=SUM(INDEX('Annual Invoice Breakdown'!\$D\$3:\$Z\$5,0,ROWS(\$1:1)))

or,

=SUM(OFFSET('Annual Invoice Breakdown'!D\$3,0,ROWS(\$1:1)-1,3))

Copied down

Regards
Bosco
#5
03-06-2019, 01:33 PM
 p45cal Windows 10 Office 2016 Expert Join Date: Apr 2014 Posts: 280

Quote:
 Originally Posted by JVenz0913 The data I am trying to pull is on another sheet within the same book. I just need the Letter of the column to change once i copy and paste the formula down.
Ah yes, I forgot the sheet ref:
=SUM(INDEX('Annual Invoice Breakdown'!\$D\$3:\$Z\$5,0,ROW()-13))

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post BertLady Word Tables 1 12-01-2018 11:44 PM sjp1966 Excel 9 04-13-2017 06:01 AM LupeB Excel 1 10-22-2015 03:02 PM krisjah Excel 1 01-25-2014 11:08 AM simville02 Drawing and Graphics 1 09-29-2013 06:33 PM

All times are GMT -7. The time now is 01:50 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top