• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding an item to an array in a formula (for a validation)

Neil B

Neil B

New Member
Hi,

Not sure if the title covers this... haveing difficulty explaining it!

In my workbook I have several sets of data in a table, say Data A through Data C, and I have a dynamic dataset called Variance which the user can set to be one of the prior 3 minus another (e.g. Data C- Data A). The idea is this is expandable and as I move through the year my number of datasets will expand.

Ok, so to the question - I have a list of all the datasets entered so far ("look_DataSets") and I want to send them to a validation drop down, but I also want to have Variance as an option in my drop down.

My current validation formula is:

=OFFSET(INDEX(look_Datasets,,2),,,COUNTA(INDEX(look_Datasets,,2)))
Click to expand...

(I am looking at second column of look_Datasets as the first is a short hand in the table and the second is the formal name for the Validation). At the moment this will give:

  • Data A
  • Data B
  • Data C
As option in the validation cell, althought the count lets it expand as the number of datasets expands. What I want to do is add "Variance" as a 4th option for the drop down. Can I add it somehow to the validation formula?

Thanks in advance, hope it makes sense!
 
Hello, and welcome to the forum!
Are Data A, Data B, ..., Variance a specific cell, or are they ranges?
 
Hello, thanks!

They are actually a lookup value in a table.

Maybe to help explain, in my table I have lines that are:
Dataset || Asset || Metric || 2015 || 2016 .... 2020 || 2021
Click to expand...
There's a bit more - monthly 2015, quarters, ytd being calculated etc. Then I can use the table to populate my reports and various analysis, etc. I have say 10 assets and 40 metrics, 400 lines of data for each dataset. I use the dataset value to decide which version of the data I want to look at. And as said above, Variance is a calculated dataset within the table.

So by choosing which dataset you want in the validation dropdown, you can popoulate the reports.

To be clear, I could very easily just take the list of datasets and combine them via normal excel formulas in another sheet and use that to feed the Validationm however I was looking to see if there is a neat formula method to just add "Variance" in to my validation formula.

That a bit clearer? I might have to make an mini-example and attach...
 
You must log in or register to reply here.
Back
Top