INDIRECT Formula for auto categorize

[dsmith616]

I am using the below INDIRECT formula (to help auto-categorize bank transactions) where the list of categories are on the same tab

=OFFSET($G$2,SUMPRODUCT(--ISNUMBER(FIND(INDIRECT($J$1),B2,1))*ROW(INDIRECT($J$1)))-2,1)

To avoid too much information being on one tab, I wanted to move my list of categories over to its own separate tab, called "Index". However, when I attempt to update the formula to point over to the new tab I am receiving mix or no results.

=OFFSET(Index!$A$2,SUMPRODUCT(--ISNUMBER(FIND(INDIRECT(Index!$D$1),B2,1))*ROW(INDIRECT(Index!$D$1)))-2,1)

Index!$A$2 = Keyword Header
Index!$D$1 = The reference to range for the keywords (ie A2:A14)

Any idea where the issue is? Or need any other info to help figure this out? Nothing has changed except moving my list to a new tab.

[TMS]

Please see the yellow banner at the top of the page.

[janmorris]

this is a wild guess (as INDIRECT is a "fix" for when referencing a range on another sheet)...

Please Login or Register  to view this content.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[dsmith616]

sample attached

[AliGW]

Your new title is marginally better. In the PM to you, when you asked how to improve it, I suggested that you articulate in it the issue you are having with a formula not working the same across two sheets as it does when used on one. The INDIRECT function may or may not be the reason, and is really not relevant in the title.

I will accept your new title, but please make an effort to make your thread titles useful and descriptive in future. Thanks.

[MarvinP]

Hi dsmith616 and welcome to the forum,

I do this problem a little differently. I use an Advanced Filter on my data and put asterisks before and after my search word. See the attached where you can put "*HOA*" in the criteria cell at I2 and then do an Advanced Filter and copy it to the right range. I've also given you a sum of those bills/payments in J7. I have thousands of rows of past check and credit transactions in my data. You can have multiple "OR" transactions by putting more criteria in I3, I4 etc. Time to learn how to do Advanced Filter? See my example:
Advanced Filter for Budget Payments.xlsx

I've also written some VBA behind my sheet so I can automatically put the asterisks in and a do the Advanced filter. If I want a category of "Food" then I use "*Safeway*" and "*Haggens*" and etc.

[TMS]

Search on the same page:

Please Login or Register  to view this content.

Search on the INDEX sheet:

Please Login or Register  to view this content.

You are better using INDEX than INDIRECT as a) INDEX is non-volatile, whereas INDIRECT is; b) INDIRECT is more complex to set up and inflexible (IMO)

[menem]

Please try

=IFERROR(INDEX(Index!$B:$B,AGGREGATE(15,6,ROW(INDIRECT("'Index'!"&Index!$D$1))/(1/FIND(INDIRECT("'Index'!"&Index!$D$1),$B2)>0),1)),"")

if you wish to modify from your original formula
=OFFSET(Index!$A$2,SUMPRODUCT(--ISNUMBER(FIND(INDIRECT("'Index'!"&Index!$D$1),B2,1))*ROW(INDIRECT("'Index'!"&Index!$D$1)))-2,1)

Regards.