Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

excel - Have one cell represent a cell range

Excel 2003 Question:
I'd like to have one cell represent a range of cells. So that another formula can use it.

Right now, I have an equation to sum the hours worked on a specific project. It looks for a project name, and then beside it, I have a column with how long i've spent on it. This is the formula =SUMIF(D1:D10,"project1",C1:C10)

If you look at the first picture,
I want B10 to say the range of Duration (B23-B28)
I want C10 to say the range of Project (C23-C28)

Now look at the second picture, with the formula.
Instead of having the range manually, I just want it to look at B10, and C10 so I get the range once. This way, I only have to change the range in one spot instead of how ever many projects I have.

I'm sorry if this is a messy problem. I'm not sure how to ask it, but I will be quickly answering comments if people aren't clear on what I'm trying to accomplish.

enter image description here



enter image description here

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Making it a bit more clear. You can enter the range (Address) as a text in the cells, and then use INDIRECT to reference them to a range instead of just text.

So enter

"C23:C46" in cell B10 (without quotes)
"D23:D46" in C10      (without quotes)

and use INDIRECT in your formula for SUMIF
Here's a picture for more clarity.

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...