• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Indirect Formula and Named Ranges

A

alanj

New Member
I used named ranges on a table, using the "create from selected" method, selecting both "column" and "rows".


The top row are months (Jan, Feb), and the Columns are names a complicated "G_All_Blah_Blah". I want to create a sumifs formula on another page that can cross reference a single cell (and a range), based on parameters from drop down menus that reference the named columns and rows. I realize I can use the indirect formula, but how can I do this to find a cross reference between columns and rows, using named ranges?


The sumif formula will have logical conditions that reference the drop down menus, which, in turn, are the names of columns and rows in the named ranges.
 
The easiest way is probably using a combination of INDEX and MATCH. The INDEX function uses an array and you then define the row and/or column position, while MATCH lets you find the position of a value within an array. Something like this:


=INDEX(B2:E10,MATCH(RowChoice,A2:A10,0),MATCH(ColumnChoice,B1:E1,0))


This is the best way to return a single value. Upon re-reading your post, I may be misinterpreting, and you need to sum multiple cells. In which case, you could do something similar, but with SUMPRODUCT


=SUMPRODUCT((A2:A10=RowChoice)*(B1:E1=ColumnChoice)*(B2:E10))


This would sum all the cells within B2:E10 that have the correct column and row labels.
 
Hi Luke M & alanj,


..sorry for interruption, just one step ahead of Luke's formula, check out this worksheet:

http://dl.dropbox.com/u/60644346/ALANJ_Luke%20M%27s%20Formula.xlsx


Regards,

Faseeh
 
Is it possible to reference a "named range" in a formula from drop down menus? My Columns are A,B,C,D, etc. My Rows are Jan, Feb, Mar...

If I just write =A Jan - it gives me the correct value. But I want this formula to point to drop downs with the values "A" and "Jan"

The attached has the examples:

http://dl.dropbox.com/u/7217416/Question_Named_Ranges_Formula.xlsx
 
Hi, alanj!


First of all, very clear and user friendly problem explanation!


Second, many issues not related to the solution:

a) when trying to download the file, Excel displays a message that certain part of your workbook is unreadable, prompts for trying to recover the book and displays this message "Registros quitados: Rango con nombre de /xl/workbook.xml parte (Libro)", in english something like "Removed records: named ranges with name of /xl/workbook.xlm part (Book)"... you should fix the workbook, even if this removals don't affect functionality

b) when playing with E5 cell formula, there are wrong or missed named ranges (C is C_, F doesn't exists)


Third, the solution? I don't know if I have reached that point yet.

a) try this formula:

=DESREF($D$10;COINCIDIR(J13;Table1[Range];0);COINCIDIR(K13;Table1[[#Encabezados];[Jan]:[April]];0)) -----> in english: =OFFSET($D$10,MATCH(J13,Table1[Range],0),MATCH(K13,Table1[[#Headers],[Jan]:[April]],0))

(If not Headers try Headings, I use a Spanish version of Excel and that doesn't tranlates).

b) download this file, if a) doesn't work.

http://dl.dropbox.com/u/60558749/Indirect%20Formula%20and%20Named%20Ranges%20-%20Question_Named_Ranges_Formula-2%20%28for%20alanj%20at%20chandoo.org%29.xlsx


Regards!
 
SirJB7,


Awesome problem solving. Your way works crisply and without trouble.


I'm still hung up on using named ranges from drop down menu references in a formula.

Since there's an error with the named ranges, could you see the file again with the named ranges removed? You can add the named ranges on your side, and maybe we can figure this out. I think there are a number of cool applications for using named ranges with drop downs.

http://dl.dropbox.com/u/7217416/Question_Named_Ranges_Formula.xlsx

Thanks again.
 
Hi ,


Using your own procedure for creating the various range names , putting in the following formula :


=INDIRECT(K13) INDIRECT(J13)


returns a value of 9 , which is the value in E11. Is this what you are looking for ?


Narayan
 
Hi, alanj!

I did it and I get two ranges with wrong names: C_ and F_ instead of C and F. However, I changed A, B, ... values for ROWA, ROWB, ..., repeated the name creating process and worked fine. So I assume it's an internal Excel problem with C & F. If this aren't the real values as I guess they aren't, I'll suggest you to go on without looking backwards. But if these are actual values, well, for the moment my recommendation is to manually change them and if I find out what's the problem I'll advise you.

Regards!
 
SirJB7, Luke M, Faseeh and NARAYANK991,


Thanks for your help. It works. Now I can use a formula that references a drop down menu, which references a named range.


Awesome.
 
You must log in or register to reply here.
Back
Top