Return cell value based of row Named Range and Column Named Range

Cooki

New Member
Joined
Jul 31, 2018
Messages
18
Hi All

I want to return the cross reference of 2 named ranges.

There is a sheet called Front Page where i want to return the result and the information is on a sheet called Commission

Row named range = Partner
Column named range = Finance

Both of the named ranges will be referenced in as cell that has been populated by another formula.

So Partner will be taken from a drop down menu in cell $B$2

and Finance will be taken from cell $C$8

I thought the formula would be something like the below, but return #VALUE so im wrong obviously :(

=INDIRECT(ADDRESS(Partner,Finance))

=INDIRECT(ADDRESS($B$2,$C$8))

Any help would be very much appreciated
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,776
The formula
=Partner Finance
will return the intersection of the two named ranges Partner and Finance.
The space is Excel's intersection operator.
 

Cooki

New Member
Joined
Jul 31, 2018
Messages
18
Perfect thank you

How about instead of saying Partner is put B2 which is where my drop down menu is with all the names of the row rangers Partner, Partner_1, Partner_2

and finance is C8 which is a formula which brings back the names of my column ranges Finance, Finance_1, Finance_2

I did try =B2 C8 which i thought would pick the named ranges from those cells, but does not work.

Is there a way?
 

Cooki

New Member
Joined
Jul 31, 2018
Messages
18
Works perfect thank you very much

Been at this for a day or so.
 

Forum statistics

Threads
1,081,556
Messages
5,359,549
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top