by
Evans Otalor , Business Analyst / Facilitator , Marina Business School
There are two major ways you could do this. You could name the range of data and use the name to refer to it in your formula or you could use an INDEX function to refer to the entire row or column in a single cell and array enter the formula.
To name a range, choose the range, go to Formulas - Define Name, click Define Name and choose a name for the range. The chosen name becomes available for you to use in a single cell and it will refer to a range.
The INDEX Function option is what I believe you are asking about and you can refer to an entire Row/Column of data this way.
{=INDEX(Range, Row Number, Column Number)} leave the Row number or Column number blank to refer to the entire row or column. Remember to array enter the formula (Ctrl + Shift + Enter).
Eg. {=INDEX(A1:D10, ,3)} will return the data in C1:C10 which is an entire column in the specified range.
{=INDEX(A1:D10,2, )} will return the data in A2:D2 which is an entire row in the specified range
Note: Only the first item will be visible in the cell but the other data are also available in the cell.
I suggest you name the range of data and use it in your formula if you want to use it as group of data or else use the INDEX Function (Array type) to refer to entire rows or columns of data. These are the 2 best ways to do it.
can you clarify more
you have sum, sumif, count, counta, countis, vlookup, hlookup
send me an example of what exactly you want to do and i'll send you the answer.
