Custom Function?:moving between tabs based on a named range

whr4th

New Member
Joined
Nov 8, 2004
Messages
20
This is a long shot - but I thought I would give the smart guys on the board a shot.

Background: I have financial data for multiple properties that is too big to fit onto one worksheet (not enough columns and turning it to rows won't help for long if I add more properties) :oops: . I have been using a combination of Offset/Index/Match to lookup the data based on which property is being examined. I now have too much data and cannot use the same simple methodology and will have to put each property's data on a separate tab.

Goal: To create a function (b/c I don't believe I can use standard excel functions) that, based upon the results of a particular named range (which is manipulated via a combo box), will start the offset function from a cell on a specific tab. When the user changes the property, the formula has to change its offset starting point from a new tab. :eek:

My hope was that I could essentially recreate my original formula (see below) with a custom function. Since I have not done many custom functions I was hoping for some ideas.

Current formula and definition of terms:
OFFSET('[TTM Database.xls]Database'!$A757,0,INDEX(List3,MATCH(PropName,List2)))

'[TTM Database... = filename with static "Database" tab. This is the section that needs to move based on the property chosen
Index(List3... = how many columns to move over based on the results of the
Match(PropName,List2) = finds the chosen property in the list and returns its location

Simply put, start at A757, and move over a certain number of columns based on the property chosen by the user

Please help oh board gurus. :rolleyes: :rolleyes:
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

whr4th

New Member
Joined
Nov 8, 2004
Messages
20
Re: Custom Function?:moving between tabs based on a named ra

After doing some additional research and board investigation, I believe that I can insert an INDIRECT statement into my regular formula and this should solve the problem.

Is that correct?
 

whr4th

New Member
Joined
Nov 8, 2004
Messages
20
Re: Custom Function?:moving between tabs based on a named ra

Figured it out after some board searching. The INDIRECT funciton does indeed work. I can combine the whole offset/indirect/index/match. It's an ugly formula, but it works.

I am not worthy of myself.... :pray: :pray:
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hey! Great to see you solved this on your own! :biggrin:

Take care.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top