Microsoft Office Forums need help with formula when dragging down

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-05-2019, 02:55 PM
JVenz0913 JVenz0913 is offline need help with formula when dragging down Windows 10 need help with formula when dragging down Office 2019
Novice
need help with formula when dragging down
 
Join Date: Mar 2019
Posts: 2
JVenz0913 is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 03-05-2019, 03:14 PM
p45cal p45cal is offline need help with formula when dragging down Windows 10 need help with formula when dragging down Office 2016
Expert
 
Join Date: Apr 2014
Posts: 280
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 03-06-2019, 07:46 AM
JVenz0913 JVenz0913 is offline need help with formula when dragging down Windows 10 need help with formula when dragging down Office 2019
Novice
need help with formula when dragging down
 
Join Date: Mar 2019
Posts: 2
JVenz0913 is on a distinguished road
Default 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.
Reply With Quote
  #4  
Old 03-06-2019, 08:13 AM
bosco_yip bosco_yip is offline need help with formula when dragging down Windows 10 need help with formula when dragging down Office 2016
Novice
 
Join Date: Feb 2019
Location: Singapore
Posts: 7
bosco_yip is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 03-06-2019, 01:33 PM
p45cal p45cal is offline need help with formula when dragging down Windows 10 need help with formula when dragging down Office 2016
Expert
 
Join Date: Apr 2014
Posts: 280
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
Originally Posted by JVenz0913 View Post
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))
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dragging a row changes formatting BertLady Word Tables 1 12-01-2018 11:44 PM
need help with formula when dragging down Dragging conditional formatting like a formula sjp1966 Excel 9 04-13-2017 06:01 AM
Need help with dragging a formula and changing a reference column as I drag the formula. LupeB Excel 1 10-22-2015 03:02 PM
need help with formula when dragging down Dragging cells with filters on krisjah Excel 1 01-25-2014 11:08 AM
need help with formula when dragging down Dragging Pictures into Placeholders simville02 Drawing and Graphics 1 09-29-2013 06:33 PM


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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft