Incorporate Looking Up Appropriate Table, from a list of Tables, within a Macro

[sherylt13]

Hello, looking for a solution within a macro that could be incorporated into an Index/Match formula. My challenge is, I run reports for varied groups, that have different "guidance" information, so my lookup for particular guidance information is dependent on the group for which I am running the report.

In the attached, I have built in Index/Match formulas in columns BT and BU, and the formula could be looking up one of three tables on the "Guidance Look Up" tab. Is it possible to build into a macro, perhaps a message box (or anything of the like, a find/replace, etc.), that could help choose the appropriate table to incorporate into the Index/Match formulas in columns BT and BU?

Thanks for any input!
Sheryl

[MrShorty]

Is it necessary to use a macro? I would probably just use two MATCH() functions with the INDEX() function:

1) One MATCH(), just like you are currently using to return row number, that finds the row number. I note that the leftmost column of each "table" is the same, so it may be adequate to simply search the first column of the table independent of which table the return value needs to come from. =MATCH($BS71,'Guidance Look Up'!$A$2:$A$10,0)
2) A second MATCH() function could then find the leftmost column # of each lookup table. =MATCH("communications",'Guidance Look Up'!$A$1:$K$1,0) [or enter communications into a cell and reference that cell]. Note that the return column for low% will be 1 column more than the result of this match and the high% column will be 2 more.
3) Then an INDEX() function referencing all of the lookup tables to return the desired value. INDEX('Guidance Look Up'!$A$2:$K$10,result_of_1, result_of_2 +1) to get the low% result. INDEX(...,...,...+2) to get the high% result.

No macros needed. Is that something you can work with, or is there more to this?

[sherylt13]

Thank you for the quick response. This is another way to go, my challenge is, I am looking for an automated way to update those formulas, which is why I was thinking of incorporating within a macro I already have (not part of the attached). I run this type of report for about seven different groups, meaning there will be about seven different tables, so depending on the group (such as "Communications", "CIO", etc.) I run for, that is how the formula should update. I am trying to avoid manually updating with each report run since these are run at a high volume.

Hope that made sense. Any thoughts? Thanks again.

Sheryl

[MrShorty]

I am looking for an automated way to update those formulas

I am not sure I understand what you mean. If calculation is set to automatic, then these formulas should update automatically whenever the data is entered/changed/updated. If you put your group name into a cell ("communication") and correctly reference that cell in the step 2 MATCH() formula, then changing the group should be as easy as entering/editing the text in that cell (a data validation drop down list might be valuable here). Updates should be automatic and the only interaction with the sheet should be entering the text in that cell (and any other inputs that your current example does not illustrate).

I think that we may need to better understand how you are interacting and/or want to interact with this spreadsheet and how these lookup functions fit into the overall project. A pivot table approach may be preferable (especially for long term database maintenance and management) or there may be other considerations.

[sherylt13]

Yes, probably confusing. Appreciate you trying here for me. The way this works is I will run a high volume of reports and paste into an Excel template, such as the sample attached. From there, I press a button and lots of formatting and varied updates occur. Because of the high volume of reports run, I am trying to remove any manual intervention, which in this case, would be updating the formulas in BT:BU with each run. Does that help in terms of what I am trying to accomplish?

Sheryl

[jaslake]

Hi sherylt13

Here's a different approach...notice the new Sheet "Lists". It contains a Dynamic Named Range called "Dept"...you can call it what ever you wish. It's used to populate the Drop Down in Cell BS3 of Sheet "RosterCurrentLast". Add as many items to it as you wish.

This "Change Code" is in Sheet "RosterCurrentLast" Code Module. As Cell BS3 changes so does Data in Columns BT and BU.

Please Login or Register  to view this content.

Let me know of issues.

[sherylt13]

Thank you !!!!

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.