Menu
I have on a sheet a range of numbers, from that range, how can I reference a full column or row given by index ?
I am using 2007.. I looked into HLookup/Vlookup/Index without success
994
When you are working with an Excel worksheet that has a variable number of rows, you may want to refer to all of the cells within a specific column. To reference the whole column, just type a column letter twice and a colon in between, for example A:A.
The COLUMN Function[1] in Excel is a Lookup/Reference function. This function is useful for looking up and providing the column number of a given cell reference. For example, the formula =COLUMN(A10) returns 1, because column A is the first column.
Excel's INDEX function allows users to reference values in a range of data (or array of data) by their column and row number position within that range. As a simple example, the formula =INDEX(A1:F10, 4,4) would return the value in the fourth row of the fourth column in that specified data range.
I'm guessing you mean referencing the whole column/row as a range/array in another formula?
If so, the only way i can think of would be to use OFFSET
=OFFSET($A:$A,,MyColIndex-1)
=OFFSET($1:$1,MyRowIndex-1,)
thats...
=OFFSET(reference,rows,cols,[height],[width])
However this won't work if you insert a column to the left of A, or a row above 1.
EDIT: just found a way around that little problem...
=OFFSET($A:$A,,MyColIndex-Column($A:$A))
=OFFSET($1:$1,MyRowIndex-Row($1:$1),)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
