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
Please help oh board gurus.
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
Please help oh board gurus.
