Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a table 2x5 cells that I want to make available for read only purposes on multiple worksheets. Is this possible to do in Excel?

I know I can give a name to a set of cells but I don't know how to insert that set.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Not sure what you mean by insert, but if you want to reference a named range, you just have to type the name of the range, e.g Sales05 / Sheet1!Sales05 (or Sheet1.Sales05 in Calc).

If you want to reference an unnamed range, you just put a colon between the endpoints of the range, e.g. A1:Z26 / Sheet1!A1:Z26 (or Sheet1.A1:Z26 in Calc).

If you want to display a range from one sheet in another range of cells on another sheet, then the easiest way I know of to do this would be to create a relative reference in the first cell of the display range to the first cell of the source range (e.g. Sheet1!A2); copy it; then just paste it into the rest of the range.

Unfortunately, there's no way to do this so that it automatically spreads into new cells when you increase the original range—at least not without scripting. So if you foresee the original range expanding in the future, it's best to just paste the relative reference into as many extra rows/columns as you think it might expand to.

You can, however, make named ranges auto-expand/contract using OFFSET():

  • Expand down as many rows as there are numeric entries: =OFFSET($A$1,0,0,COUNT($A:$A),1)
  • Expand down as many rows as there are non-empty entries: =OFFSET($A$1,0,0,COUNTA($A$A),1)
  • Expand down to the last numeric entry: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)
  • Expand down to the last text entry: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

The above link also has formulas for expanding one row per month/week.

share|improve this answer
    
How do I reference the named or unnamed range so that the entire range will appear? –  RWL01 Apr 16 '12 at 14:18
    
@RWL01: Unfortunately, you can't display a single formula across multiple cells without putting that formula into each of those cells. The easiest way to do it would be use relative references. –  Lèse majesté Apr 16 '12 at 14:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.