Number of a rows in a range reference using a formula?

[ubundom]

My formula is:

=CELL("address",Sheet1!B2:E4)

and Excel correctly shows the address of the top left cell in the range:

[Test.xls]Sheet1!$A$1

I would like to use a formula that will tell me that the number of rows in this range is 3 and the numbers of columns is 4, but, without using a macro or having to define a range name. Any ideas?

 

[jimbojones]

1

 

[ubundom]

1

As a starter for one ... what is your idea jimbo?

 

[VoG]

Try

=ROWS(Sheet1!$B$2:$E$4)

=COLUMNS(Sheet1!$B$2:$E$4)

 

[ubundom]

Re: Number of rows in a range reference using a formula?

Try

=ROWS(Sheet1!$B$2:$E$4)

=COLUMNS(Sheet1!$B$2:$E$4)

Yep! that works, but I would prefer to have formula that "extracts" the dimensions of the range in the formula that is stored in the cell as described. I.e.: get the height and width in rows and columns.

Um, it might not be possible in a formula; it is trivial in VB but I must avoid macros, for all the usual reasons.

Thanks for your interest.

 

[VoG]

Re: Number of rows in a range reference using a formula?

I would prefer to have formula that "extracts" the dimensions of the range in the formula that is stored in the cell as described.

Sorry but I have absolutely no idea what you mean by that

 

[ubundom]

Re: Number of rows in a range reference using a formula?

Sorry but I have absolutely no idea what you mean by that

Oh sorry ... more explanation: I am interested in processing a bunch of ranges, these are:

Sheet1!A1:E4
Sheet1!B5:G6
Sheet2!B7:F10

I have stored these in a table in a table (in Sheet3 say) thus:
=CELL("address",Sheet1!A1:E4)
=CELL("address", Sheet1!B5:G6)
=CELL("address", Sheet2!B7:F10)

I have done it like this so that:
a) each range is only stored once (inside the formula) and
b) so that I can access the workbook name (in an XSL processor) thusly:

[Test.xls]Sheet1!A1:E4
[Test.xls]Sheet1!B5:G6
[Test.xls]Sheet2!B7:F10

I can use ROW(INDIRECT(range)) and COLUMN(INDIRECT(range)) to give me the start row and column of a range that is found in the bunch of ranges at the top of this reply. Now I need: LASTROW(INDIRECT(range)) and LASTCOLUMN(INDIRECT(range)).

This will avoid having to have (the obvious but inefficient) further column containing the range (a second instance) and therefore be prone to "user error" and other mistypings.

Thanks so much for your ongoing interest!

 

[VoG]

With your example of B2:E4 does this help?

<b>Sheet14</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:129px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td >Row</td><td >Column</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >No of rows</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Start</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >End</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=ROWS(Sheet1!$B$2:$E$4)</td></tr><tr><td >C2</td><td >=COLUMNS(Sheet1!$B$2:$E$4)</td></tr><tr><td >B3</td><td >=ROW(Sheet1!B2:E4)</td></tr><tr><td >C3</td><td >=COLUMN(Sheet1!B2:E4)</td></tr><tr><td >B4</td><td >=B2+B3-1</td></tr><tr><td >C4</td><td >=C2+C3-1</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[ubundom]

With your example of B2:E4 does this help?
Sheet14
<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 129px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height: 20px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td>Row</td><td>Column</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>No of rows</td><td style="text-align: right;">3</td><td style="text-align: right;">4</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Start</td><td style="text-align: right;">2</td><td style="text-align: right;">2</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>End</td><td style="text-align: right;">4</td><td style="text-align: right;">5</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0); width: 196px; height: 156px;"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt; width: 179px; height: 116px;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B2</td><td>=ROWS(Sheet1!$B$2:$E$4)</td></tr><tr><td>C2</td><td>=COLUMNS(Sheet1!$B$2:$E$4)</td></tr><tr><td>B3</td><td>=ROW(Sheet1!B2:E4)</td></tr><tr><td>C3</td><td>=COLUMN(Sheet1!B2:E4)</td></tr><tr><td>B4</td><td>=B2+B3-1</td></tr><tr><td>C4</td><td>=C2+C3-1</td></tr></tbody></table></td></tr></tbody></table>

That would be all OK if I didn't think that someone might inadvertently mis-key the B2:E4 and so I was hoping for a formula that would be like this so that there is only one instance of the range names stored in the CELL formula in column A:
<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; width: 481px; height: 97px;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 129px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td style="vertical-align: top;">D
</td><td style="vertical-align: top;">E
</td></tr><tr style="height: 20px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Address
</td><td>RowStart</td><td>RowEnd</td><td style="vertical-align: top;">ColStart
</td><td style="vertical-align: top;">ColEnd
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>[Test.xls]Sheet1!$B$2</td><td style="text-align: right;">2</td><td style="text-align: right;">4</td><td style="vertical-align: top;" align="right">2
</td><td style="vertical-align: top;" align="right">5
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>[Test.xls]Sheet1!$A$10</td><td style="text-align: right;">10</td><td style="text-align: right;">16</td><td style="vertical-align: top;" align="right">1
</td><td style="vertical-align: top;" align="right">6
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>[Test.xls]Sheet2!$C$2</td><td style="text-align: right;">2</td><td style="text-align: right;">4</td><td style="vertical-align: top;" align="right">3
</td><td style="vertical-align: top;" align="right">5
</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0); width: 196px; height: 156px;"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" width="226" border="1" cellpadding="2" cellspacing="0" height="169"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>A2</td><td>=CELL("address",Sheet1!B2:E4)</td></tr><tr><td>B2</td><td>=ROW(INDIRECT(A2))</td></tr><tr><td style="vertical-align: top;">C2
</td><td style="vertical-align: top;">=COLUMN(INDIRECT(A2))
</td></tr><tr><td>D2</td><td>=LASTROW(INDIRECT(A2))</td></tr><tr><td>E2</td><td>=LASTCOLUMN(INDIRECT(A2))</td></tr><tr><td>A3</td><td>=CELL("address",Sheet1!A10:F16)</td></tr><tr><td valign="top">B3
</td><td valign="top">=ROW(INDIRECT .... etc
</td></tr></tbody></table></td></tr></tbody></table>