CODEWITHSUNDEEP
excel-formula
dav
dav

Reputation: 190

Use cell text to build Excel formula

I'm trying to utilize a row and a column header to build a function that I can fill across an excel table (range of cells, not excel table object). So, for example I have a row header "BAT61" and column headers "A","B","C"...

I'd like to create a formula that generates the following results in those columns: =min(BAT61A),=min(BAT61B), =min(BAT61C)... Where those generated names match named ranges that have already been created.

I've attempted to use =index without success (I generally get a #ref error). I'd prefer a non-vba solution, if at all possible. Any help is appreciated.

UPDATE: I've tried the indirect function but gotten the same #ref error. I've verified that the named range exactly matches the text string I'm trying to pass. I've tried the following variations with the same result #ref: =min(indirect(A1))[where A1 is the cell with "BAT61A"], =min(indirect(BAT61A))[to try and get the result directly], and =min(indirect(A1&A2))[where A1="BAT61" and A2="A"]. But, when I create a "Test" range, like suggested-it works. Any further ideas would be appreciated.

UPDATE2: I was able to get it working with several slight modifications. My named ranges that did't work were actually referring to fairly extensive array formulas that were parsing select rows from a table. When I created new dummy columns in the table that basically did the filtering, the indirect would pull the correct columns based upon the indirect.

Upvotes: 1

Views: 31870

Answers (2)

Teela
Teela

Reputation: 153

For reference of others, use the function "Address" and "Indirect" in combination to achieve the above behavior.

Here is a sample for using values in cells to create formulas

=INDIRECT(ADDRESS(E6,K2))

Assuming cell

E6 holds 3 and K2 holds 4

The above formula will return

=INDIRECT($C$4)

Assuming cell

C4 holds This is a test

The above formula will return

This is a test

Upvotes: 1

Chriseyre2000
Chriseyre2000

Reputation: 2053

Name some cells "Test" then you can use:

=MIN(INDIRECT("Test"))

http://www.cpearson.com/excel/indirect.htm

The name can be constructed using the & operator = $A1&B$2

Upvotes: 4

Related Questions