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

#### whr4th

##### New Member
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) . 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.

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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### whr4th

##### New Member
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
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.... ray: ray:

#### Zack Barresse

##### MrExcel MVP
Hey! Great to see you solved this on your own!

Take care.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,969
Messages
5,856,584
Members
431,821
Latest member
chaftun

### 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.

### Which adblocker are you using?

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

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