I'm attempting to create a Custom List from a Data Set - let me explain.
Original Data Table:
Solution I'm Seeking:
My parameters would be to return the (1) Column Label, (2) Column Header, & (3) Amount for any
absolute values in the data set greater than 5,000,000.
Just to be clear - (6,000,000) would be included in the
Custom List since the absolute value is 6,000,000 which is greater than 5,000,000.
After much research (Array
Formula's are not my forte...), I put together the following array formula:
This will give you the Row Reference Number
for each instance of a value greater than 5,000,000
I pasted above the corresponding value
from the Data Set for ease of understanding...notice the numbers in RED - those are Subtotal lines...which I would love to
efficiently exclude from my Custom List...but I have no idea how! lol That's really a side problem I'm not worried about
right now, but if someone wants to include a fix in the solution I would be grateful.
THE MAIN PROBLEM:
So now that the above formula returned the proper Row Reference Number - I thought to use Index Function in order to return
the cells actual value within the array thus completing a pivotal piece of my Custom List:
But as you can see in the screenshot - The Column Reference Number is not returning the correct
value...it's simply repeating Column Reference "4" over and over again...instead of changing to 6, 8, 10, 12, 14,
or 16 - depending upon where the greater than 5M value is located in the Data Set.
How should the Array Formula be
modified in order to return the correct Column Reference number?
(Again, see 'Solution I'm Seeking:"
screenshot above for reference to what I want the final solution to look like)
Is there a more efficient way of
creating a custom list like this? I'm really trying to learn Array formula's...so if someone can modify my formulas to make
it work using an Array - that would be perfect!
Thanks for help.