MrExcel Publishing
Your One Stop for Excel Tips & Solutions

using the sum function with data that changes


Posted by Nancy on January 24, 2002 1:42 PM

Okay, this is not so easy to explain. I am trying to add up numbers in a row using the sum function. The thing is that every monday the range changes, which would mean that I would have to go in and change my function. I don't want to do that. I already inserted an object of dots which will jump to the right day every monday. Now I would like my sum function to pick up from where ever my object drew some dots. I hope this makes sense. Please, if anybody has any idea... Thank you


Posted by anno on January 24, 2002 2:14 PM

sounds like a dynamic named range to which your sum function refers will do the trick but i don't get the thing about the dots. care to explain further? (nt)

Posted by Nancy on January 24, 2002 2:23 PM

Re: sounds like a dynamic named range to which your sum function refers will do the trick but i don't get the thing about the dots. care to explain further? (nt)

Honestly, I don't know what you mean by a dynamic named range. How do you set that up? The thing with the dots wasn't very clear, was it? Sorry. Every Monday the my spreadsheet moves to the right date, you can still see last weeks data but the dots(an object) show you which week you are in right now. That's the code for it. It takes the date from another sheet and compares it.
With Worksheets("projects")
.Shapes("Group 63").Left = .Columns(Worksheets("projects").Range("b5").Value).Left
End With
Maybe I just made it even more confusing, but if you could explain the dynamic named range to me, I would appreciate it...
Thanks
nancy


Posted by anno on January 24, 2002 5:45 PM

Re: sounds like a dynamic named range to which your sum function refers will do the trick but i don't get the thing about the dots. care to explain further? (nt)

hi nancy
sorry - didn't mean to confuse you with jargon. if you go to this post: 15032.html you'll see some code for establishing a dynamic range. i thought this might make sense to you as a vba user (i'm not so i don't get it). however if you want to do it the old-fashioned (ie. non-vba)way, go to the ozgrid site from the link in the post i mentioned above and you'll find probably more than you'll ever want to know about dynamic ranges. i think aaron blood's site has some stuff on them as well but i can't recall his address - google should find him though.
good luck
anno


Posted by Nancy on January 25, 2002 7:34 AM

Re: sounds like a dynamic named range to which your sum function refers will do the trick but i don't get the thing about the dots. care to explain further? (nt)

Hi Anno
Thank you for your help!!!! It's really nice of other people to take their time to answer somebody's question!! Thank you!!!!!!!!! hi nancy