Solved: see bottom for follow up
Hi all,
First post to this forum so please forgive any formatting or other fumbles.
I am trying to find a way to dynamically pull values that appear between 2 cells that will have static text values, but variable locations. The data is from an old legacy system that does not display data in a consistent structure and each individual store in the system will have different, user defined, categories.
In the file attached, there are 2 distinct revenue centers, "Other Income" and "Store Details", each with their own distinct and customizable charge codes. Each store can add, subtract and customize the name of the charge codes under each revenue center.
This information is exported into the same report and will appear different for each unique store and each unique store may have a different number of rows in the original report.
In the attachment, the table on the left has a column named "charge type" and I need to get the categories that show up in the report between the cells that say "Store Details" and "Total Store".
I hope I have made it clear what I am trying to get. Since the extracted data will go into another report which needs to be done in mass, the least we have to manually fiddle with the data that comes from the original system report the better.
Thank you!
Solution found after posting:
I answered my own question almost immediately after posting. Using another cell to determine the row # of the cell that "Store Details" and "Total Store" appear I can add a column to get that row as a # value for each row heading in the report. Then using if> and if< I was able to pull the unique list in another table. I attached an example that shows the formulas in use.
Bookmarks