CODEWITHSUNDEEP
excelexcel-formula
Sam
Sam

Reputation: 497

Excel how do I make COLUMN() function return column from named range

I am referencing values in a 7x7 RANDARRAY() that starts at I2 using an Index formula to populate values in a Table range in I12:P19. The formula that I have works but in order to port it to different tables throughout the workbook, I tried using the ROW() and COLUMN() functions thinking that it would reference the row and column of the table range and the INDEX() would return the corresponding value from the array.

The issue is that if the table range is I12:P19 the ROW() and COLUMN() functions think that I am at Row 12 and Column 8 which quickly put my values outside of the 7x7 RANDARRAY()

Here's a screenshot of the tables: enter image description here

The first column in the bottom table should return 38 which references value 38 in another table (and returns a State name) but when I use Evaluate Formula the formula indicates that I am already in the 9th column which is outside of the 7x7 array and so I get an error ("FREE" is the placeholder text for an IFERROR() function that surrounds the formula).

The question then is: how do I, or can I, use COLUMN() and ROW() to reference the column and rows of the table range itself?

The formula, that works, as shown in column 1 of the range, is: =INDEX(tblStateCap,RANK.AVG(INDEX(I2#,[@blank],thisTable[[#Headers],[1]]),I2#,0),2)
I am trying to replace [@blank] with ROW(thisTable[@blank]) and thisTable[[#Headers],[1]] with COLUMN(thisTable[[#Headers],[1]])

I'm also open to easier ways of doing this.

Note: the values showing a State name use the old formula and is correct for now.

Upvotes: 0

Views: 271

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

If I have understood you correctly then the current outcome you are getting is reading from cell reference and not from the table.

However, the required outcome is as per below picture i.e. you want top left cell in table to be shown as Row 1 and Column 1 irrespective of its position in the sheet where first digit shows row position and second shows column position.

enter image description here

Simple way of doing this in the top left cell for ROW use

=ROWS($A$1:$A1)

and for columns use

=COLUMNS($A$1:A$1)

These will get updated in the table as you copy down and across. It will not be affected if you copy/move the table anywhere in the sheet.

Upvotes: 1

Related Questions