Return value based on a variable number of cells

Hi there,

This might be an impossible problem, so if it can't be done, i'm totally good with accepting that there just isn't a way of setting up the logic.

Here's a small sample of an excel doc i've been working on/with for a few months. FMIL Experimental.xlsm
I've managed to figure out logic and formulas for repeating headings and subheadings in columns in order to keep things organized and I've received help with VBA to affect font sizes. Everything that needs to work in a certain way works, but i had an idea that would save me a step, but it might be overly ambitious.

In Column E, I have a "Page" column, which references the highlighted row (in the sample, it's "Drew Estate Acid" based on what's in cell A2, and then "Drew Estate Undercrown" based on cell A30. I did something similar with the "Grouping" in column F. All of this is set up so that an app plug-in for InDesign can group and keep the data organized for when i create a monthly catalog, and all of that is preamble to what I'd like to try to do.

Column H is "Header Shield" and Column I is "Highlight. Generally, Header Shield will be based on one of the things (if any) in the highlight column. Essentially, if one of the items on the page is called out as being a "top seller" then i put "top seller" in the header shield column too, and that way the plug-in knows to both apply a color highlight to he row, and to delete all the headers except the one with the "top Seller" icon included in it. What i was thinking would be cool would be if the "header shield" column could automatically have "top seller" populate the Header Shield column if it appears in the highlight column. The problem is that i'm not quite sure how to make the formula look for "top seller" in the highlight column, but only within the items on that page (the columns under the page "Drew Estate Acid" have an example of how i've done it manually up until this point, and the shield header and highlight columns under "Drew Estate "Undercrown" are empty. Given that the number of items (and the number of subheadings) varies from "page" to "page", i can't just set a formula to look for the next "10-20 cells in column I, because 10-20 might not be enough, or it might bleed into the next "page" of items.

Like i said, i'm not sure if this is even possible, and I haven't even been able to think of a way in which the logic could work. I'm hoping there's a ways to have the formula in column H look within a variable number of cells in column I (anything between highlighted rows) to see if any of them have a label like "top sellers" in them. Any ideas would be appreciated.

Thanks everyone.

Hi, Sorry for the delayed response. I just finished with the most recent deadline so I'm now able to think about this again. You've nailed just about everything.

• I link to an "inventory" workbook because, theoretically, the data (mainly pricing, but also new products/removing old products) changes every week or so. Most of the work done in this excel doc is because the raw item names in the inventory doc aren't really compatible with the magazine I create every month. So a lot of it is to keep the information accurate/up to date while also allowing me to completely change how the item is listed. Theoretically, we're changing to a new system over the next month or so, and hopefully enough of the information will be broken out over separate columns that I'll be able to automatically build item names the way they should be without having to change that part by hand.
• I actually do have multiple sheets, each representing one category (cigars, non-cigar tobacco, alternative, accessories, etc…) I simply excluded any other sheets because they essentially all work the same way and I was trying to keep file-size reasonable.
• The duplication is due to some limitations of the software that I use to connect Excel to InDesign. For the example that you referred to, column A cannot have any repeated data, so instance that some data needs to repeat, like a subhead/description for a 20 ct. box of Maduro, I add a space to the end of the line in column A so that the software doesn't kick it back. Column B, includes a "Trim" function to remove those spaces so they don't get in the way of any other lookup functions in the excel file or in the text boxes in the final InDesign file.
• With all of that in mind, here's a quick rundown of the general setup. Column A includes item numbers, separated by "Page" (highlighted with a color fill and white 14pt. text) and "Grouping" (highlighted with bold text and a black stroke on the bottom of the row) which are then copied into column E and F respectively. Columns E, F, G, and H contain this information exclusively for being able to alphabetize the excel sheets and to allow InDesign to identify groups and subgroups. Column I and J (Header Shield & Highlight) pull from the same list in the "Menu Items" tab. Column J automatically highlights individual rows in Indesign to call out individual items, whereas Column I allows InDesign to see that a specific callout needs to be added to the header on the page. The way InDesign specifically looks at Column I to determine a header is by looking at the first item on the page, or in the case of a split page, the first and last item on a page. That's why column I needs to populate a single item from the list for the entire "page."
• Ideally, what I would do for Column I is set up a series of if/then functions so that, if the first value is false, then look for the 2nd, etc… The first function would be to look at columns M and R. If M is not blank and R is not blank, that would mean that there's 2 promotions happening on the same page, a discount and a free gift, so the header for the page would be "2x Deals" from the list in the Menu Items tab. If that's false, then it would look at column I of any row that is on the same "page" (matching value of column E, if that makes sense). I'd probably continue setting up if/then functions so that there's a level of priority so that first it looks for "new" If it doesn't find that then "Top Seller" if not that then "limited" then "exclusive" etc…

The issue that I'm having with setting up these header formulas is that it can't automatically look at the same row or the row directly above it. It needs to search all the rows where the values in Column E match, to see if there's one of the options in column J. Given all of the other logic I've been able to build in, I feel like I'm almost there, but not quite.

The system won't allow for individual files over 1mb, so I'm attaching the full inventory report that my excel doc pulls from (Current Inventory.xlsx) but only a reduced version of the excel doc with my formulas, because in full it's about 15mb (Full Month item listing reduced.xlsm).

Let me know if I can help clarify anything else.

Thanks again.