Hi all,
I'm new to this forum, hopefully I can help answer some questions and also get some help.
Right now I'm building a worksheet that essentially has a data input sheet that I want to use to store all the data for the rest of the workbook in, and then name all the ranges in that data input sheet so that I can reference them more easily. This sheet has columns that are broken down by year (row 1), month (row 2), budget or actual (row 3), and then revenue, expenses, orders and visits (row 4). The rows are broken down into brands (column A) and revenue channel (column B). The years (just 2016 for now), months, and brands are consecutive, but the actual vs budget, revenue expense orders visits, and revenue channels are not. So for example on the named columns, there are 8 consecutive columns for January (January is listed 8 times from cell C2:J2), and no where else in the sheet does January appear. However, in row 3, budget is listed in groups of 4 (from cell C3:F3, then K3:N3), while actual is listed in groups of four as well (G3:J3, then O3:R3). Therefore, these columns alternate in groups of four, and have named ranges accordingly; this is what I mean by non-consecutive named range. The groups of revenue, expenses, orders, and visits repeat every four columns as well, example: the named range for revenue is column C, G, K, ... EE, while the named range for expenses are D, H, L, ... ED.
With all that said, I can very easily create a formula using each named range with the space operator separating each, and it will return the cell at the intersection. If all of these named ranges were continuous, I could also hard-code the name of each named range into separate cells (let's say from C2:C7), then use the formula:
=INDIRECT(C2) INDIRECT(C3) INDIRECT(C4) INDIRECT(C5) INDIRECT(C6) INDIRECT(C7)
However, when introducing the nonconsecutive named range into this formula, the formula evaluates to a #REF error. I've done lot's of searching on this topic, and haven't found any definitive answers as to whether it's possible or not.
Thoughts?
Thanks,
Nick
Bookmarks