Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-05-2019, 02:55 PM
JVenz0913 JVenz0913 is offline Windows 10 Office 2019
Novice
 
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 Windows 10 Office 2016
Expert
 
Join Date: Apr 2014
Posts: 251
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 Windows 10 Office 2019
Novice
 
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 Windows 10 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 Windows 10 Office 2016
Expert
 
Join Date: Apr 2014
Posts: 251
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
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
Dragging cells with filters on krisjah Excel 1 01-25-2014 11:08 AM
Dragging Pictures into Placeholders simville02 Drawing and Graphics 1 09-29-2013 06:33 PM


All times are GMT -7. The time now is 05:47 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft