Multiple Title Search in Cells

[roc_ent]

Hi everyone,

I'm trying to design an expense form on Sheet 2 and I have 4 types of expenses in a drop down list in merged cells F-G17 with "categories" of Office, Executive, Meeting, Conference.

What I would like to do is once they choose the "category" of their expenses, merged cells I-O17 would populate another drop down list for different accounts of expenses (IE hotel, meals, etc) and in merged cells Q-R17 it would populate the account to be credited to.

All my tables with with names and account numbers are located in a table on sheet 3.


Is this at all possible, and if so how can I format this?

Thanks for your time in reading this.

 

[Toadstool]

Roc_Ent,

I might have a solution for you if I've interpreted the request correctly.

Please take a look at this 6 minute 28 second video and let me know if it's what you wanted.
https://www.youtube.com/watch?v=PvbtbIS_98s

Regards,
Toadstool
(just a fun guy)

 

[Toadstool]

It seems I broke a rule by linking to a video explaining my solution so let me try and describe it here.

The reference data is on Sheet3 in Excel tables, as described.
Each Table is then selected and Formulas, Create from Selection used to give each a name using the heading (paying attention that Names can't have spaces, hyphens, etc). The accounting code name must be the first character of that Category followed by the text "code".

Sheet3
[TABLE="class: outer_border, width: 894"]
<colgroup><col><col><col><col span="3"><col><col span="2"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Category
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Office
[/TD]
[TD]Ocode[/TD]
[TD][/TD]
[TD]Executive[/TD]
[TD]Ecode[/TD]
[TD][/TD]
[TD]Meeting[/TD]
[TD]Mcode[/TD]
[TD][/TD]
[TD]Conference[/TD]
[TD]CCode[/TD]
[/TR]
[TR]
[TD]Office[/TD]
[TD][/TD]
[TD][/TD]
[TD]Staples[/TD]
[TD]O6653-44[/TD]
[TD][/TD]
[TD]Private Jet[/TD]
[TD]E9982-11[/TD]
[TD][/TD]
[TD]Coffee[/TD]
[TD="colspan: 2"]M9292-11[/TD]
[TD]Hotel[/TD]
[TD]C7732-21[/TD]
[/TR]
[TR]
[TD]Executive[/TD]
[TD][/TD]
[TD][/TD]
[TD]Paper[/TD]
[TD]O6653-88[/TD]
[TD][/TD]
[TD]Massage[/TD]
[TD]E9982-01[/TD]
[TD][/TD]
[TD]Cake[/TD]
[TD="colspan: 2"]M9292-55[/TD]
[TD]Hire Car[/TD]
[TD]C7732-43[/TD]
[/TR]
[TR]
[TD]Meeting[/TD]
[TD][/TD]
[TD][/TD]
[TD]Stamps
[/TD]
[TD]O6653-77[/TD]
[TD][/TD]
[TD]Private Box[/TD]
[TD]E9982-44[/TD]
[TD][/TD]
[TD]Projector[/TD]
[TD="colspan: 2"]M9292-25[/TD]
[TD]Breakfast[/TD]
[TD]C7732-76[/TD]
[/TR]
[TR]
[TD]Conference[/TD]
[TD][/TD]
[TD][/TD]
[TD]String[/TD]
[TD]O6652-92[/TD]
[TD][/TD]
[TD]Opera[/TD]
[TD]E9982-91[/TD]
[TD][/TD]
[TD]Flipchart[/TD]
[TD="colspan: 2"]M9292-65[/TD]
[TD]Per Diem[/TD]
[TD]C7000-01[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cups
[/TD]
[TD]O6653-12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Laser Pointer[/TD]
[TD="colspan: 2"]M9292-22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The Expense Type LoV is created using Data, Data Validation, List and in the Source =Category so it picks up the entries from the Category table.

The Expense Account LoV is created using Data, Data Validation, List and in the Source is =INDIRECT($F$17) so in this example it takes the selected text "Conference" and INDIRECT treats it as a name to the Conference table so we get the dropdown Hotel, Hire Car, Breakfast, Per Diem.

For the Expense Account Code we INDEX into the correct Account Code table by constructing an Excel Name of the first letter of the Category (so that's why we named the account code tables OCode, ECode, MCode and CCode) and append the text "code" which then INDIRECTly access the correct accounting code teable (in this case CCode).
Then we get the row by MATCHing INDIRECTly the Expense Account row which matches.

=IFERROR(INDEX(INDIRECT(LEFT(F17,1)&"code"),MATCH(I17,INDIRECT(F17),0)),"")

Sheet2
[TABLE="class: outer_border, width: 578"]
<colgroup><col span="3"><col span="6"><col span="3"><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Expense Type LoV[/TD]
[TD][/TD]
[TD="colspan: 7"]Expense Account LoV
[/TD]
[TD][/TD]
[TD="colspan: 2"]Expense Account Code
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Conference[/TD]
[TD][/TD]
[TD="colspan: 7"]Hire Car
[/TD]
[TD][/TD]
[TD="colspan: 2"]C7732-43[/TD]
[/TR]
</tbody>[/TABLE]

 

[roc_ent]

THANK YOU!!!! ever so much! I'm sorry to hear that you were broke a rule by posting this video, however, Mr. Excel Moderators should be aware of something quite interesting relating to your video. As I was doing selling skills seminars in my "hay days" there are different teaching methods where individuals learn at the best. One is Practical where the student must work at it to understand it. The second is Audio when the student hears it being explained and that is all they need to be successful. The last method is a combination of both, which in my case is my best method of learning. In praising your video and the time you took to show and explain it will make me learn and use this much more than what you described in the written format.

I strongly believe that this site is to help one-another, and if posting a video will help me understand and learn versus simply reading it, trying it, and forget about it because I can't figure it out. Therefore, what is the harm in providing those that want to learn more about Excel, being supplied with the tools they need to succeed.

I hope the Moderators will read this comment and reflect on it. I'm certainly open to receiving any feedback they might have in regards to my reply to you and your video. Again, Thank You so much. You've helped me to help a non profit organization with keeping track of their expenses. Job well done, and what must be obvious by now, your answer was "bang on".

 

[Toadstool]

You're welcome!

I agree with you about the video (that's why I created it!) but I also understand that the MrExcel group spend time and money maintaining this resource and want their forums to be a repository of solutions, not just an index into solutions held elsewhere.

I did two videos today and a system administrator, Fluff, pointed out Rule #4 so I've added textual descriptions of my proposed solution on both answers. I also answered a third simpler question today which needed no further explanation.

I've used the MrExcel forums for a few years but must have forgotten that rule. When the solution is relatively complex and difficult to describe then I know a video helps so in future if I answer a question here I'll make sure and give a textual response along with the text of any formulae although I may add a video at the end if I think it is warranted. I'll see if the system admins find that acceptable.

Regards, Toadstool
P.S. The YouTube videos are all Unlisted so cannot be found with a YouTube search because I'm not trying to create my own video library. The link from this forum is the only way to find it. I guess the only concern for the future is what happens with my videos after I pop my clogs as Google may remove Inactive accounts if not used for 6 months or more.

 

[Peter_SSs]

I hope the Moderators will read this comment and reflect on it.

I have, and Toadstool has already pointed out some of the reasons for the rule. In case you haven't read the rule mentioned, the last paragraph states

To that effect, do not simply create a file with a solution, and provide a link to that. Some of the issues with providing links to files uploaded to the internet are: those links often expire, many users cannot download files from file-sharing sites due to network security restrictions, many users are reluctant to download files for personal security reasons and the ability to search the forum effectively is reduced.

I have highlighted the word 'simply' to emphasise the fact that the rule is not saying that you cannot post a link to your video, it is indicating that you should not only do that. Toadstool has already mentioned that we want the forum to be a repository of solutions. More than that, we want it to be a searchable repository. If this thread has stopped at post 2 and later a forum member was searching for, say, Table, Data Validation, INDRIRECT etc they would not have found this thread which may be of use to them. Further such links are often rendered useless well before somebody "pops their clogs" as they may have a time-limit expiry or the member removes the target file to free up space or some other reason.