A Robust Way To Reference Multiple Columns In a Table

A Robust Way To Reference Multiple Columns In a Table | Microsoft Share Point

A Robust Way To Reference Multiple Columns In a Table | Microsoft Share Point — Thu, 09 Apr 2009 10:47:42 GMT

PingBack from http://microsoft-sharepoint.simplynetdev.com/a-robust-way-to-reference-multiple-columns-in-a-table/

re: A Robust Way To Reference Multiple Columns In a Table

sam — Fri, 10 Apr 2009 12:14:51 GMT

Joseph,

Why cant we simply have a LastRow or LastCol Function which returns the value/Address of the LastRow or LastCol in the row or column.

So Sum(A1:LastRow(A)) would give me sum of all

cells from A1:Upto As much as there is data

=LastRow(A) gives me the value in the last row

Currently I have to do this through a UDF

re: A Robust Way To Reference Multiple Columns In a Table

Joseph Chirilov — Fri, 10 Apr 2009 23:46:51 GMT

sam: It's an interesting idea. It would work for the simple example above, but not so for a more complex table. For example, the idea for this post came as I was creating a scheduling spreadsheet, and I had a span of columns to represent individual developers, and another span of columns to represent individual testers. I needed to do calculations based on the entire set of developers and testers independently. A LastCol function would not have worked for me in this case.

re: A Robust Way To Reference Multiple Columns In a Table

Tim — Tue, 14 Apr 2009 01:04:55 GMT

This technique also works when referencing data in multiple tabs in an Excel workbook

example: sum(starttab:endtab!A1)

re: A Robust Way To Reference Multiple Columns In a Table

Joseph Chirilov — Tue, 14 Apr 2009 19:31:13 GMT

Good point, Tim. It sure does.

re: A Robust Way To Reference Multiple Columns In a Table

Steve Edwards — Thu, 16 Apr 2009 15:36:42 GMT

Why not use the OFFSET function inside your SUM or COUNT to point to the changing range? The HEIGHT and WIDTH arguments can be used to refer to any area.

For example:

SUM(OFFSET(A1,,,1,4))

Returns the total of the range starting from A1 and continuing 4 columns across to the right along the same row (The HEIGHT is 1 and the WIDTH is 4) Enter the Width argument as a cell reference and by changing the cell value you can refer to any area.

re: A Robust Way To Reference Multiple Columns In a Table

Joseph Chirilov — Fri, 17 Apr 2009 08:54:13 GMT

Steve: thanks for the suggestion. That works too, but for my way of working with Excel it's a bit more cumbersome as I'd have to remember to update the cell that contians the width if I ever add/delete columns, which just increases the chances of making my spreadsheet incorrect.

Also, OFFSET is volatile, which introduces other considerations.

re: A Robust Way To Reference Multiple Columns In a Table

Kathy B — Sat, 18 Apr 2009 03:21:48 GMT

Can someone expand on the "lastrow" formula above and also what is a UDF

re: A Robust Way To Reference Multiple Columns In a Table

wolfewa — Sat, 18 Apr 2009 08:28:15 GMT

Sorry to say, but this is excel 101 and not something that warrants discussion. sorry....but it's true.

re: A Robust Way To Reference Multiple Columns In a Table

Paul Haywood — Mon, 20 Apr 2009 07:43:25 GMT

Would this solution work when the data itself is being pulled into Excel via ODBC?

Regards

re: A Robust Way To Reference Multiple Columns In a Table

Joseph Chirilov — Mon, 20 Apr 2009 20:01:23 GMT

Kathy: LASTROW is not a function that actually exists in Excel. Sam suggested it as a possible new feature. A UDF is a "user-defined function". Basically the idea is Excel allows you to write your own funtions that can be called in spreadsheet formulas just like any other function. UDFs can be created in a number of ways, the most common of which is in VBA (Visual Basic for Applications). Sam was saying he wrote a UDF called LASTROW which tells him where the bottom of the data is on a given sheet.

wolfewa: it may not be an advanced topic but there are many Excel users out there who would benefit from this information. This blog site does not cater only to advanced users.

Paul: Yes this solution should work in that scenario.

re: A Robust Way To Reference Multiple Columns In a Table

Salvius — Thu, 07 May 2009 01:59:51 GMT

"I'd have to remember to update the cell that contians the width if I ever add/delete columns"

But you can also enter a COUNT() or COUNTA() function, to calculate the width/height based on what's actually there. For example (and just in case there are any differences, I'm using Excel 2003):

SUM(OFFSET($A$1,ROW()-1,3,1,COUNTA($1:$1)-3))

This formula (pasted into column C in each row) will correctly calculate total square footage in your first sample table, and additional rooms can be added simply by adding a "SqFt-RoomX" header in the header row and inputting the numbers. I suppose there is also the caution that the columns must be contiguous...

I use this trick all the time, usually by using the offset formula to define a named range, which can then be used in other formulas. It's great for things like dynamically resizing charts/graphs, which can use named ranges for data sources.