Solved

Excel 2007 Dynamic Dependent Data Validation Lists issues

Posted on 2010-09-21
3
799 Views
Last Modified: 2013-12-13
OK, I have a spreadsheet that's is intended to be used as an estimating sheet for painting contractors. I have Sheet 1, that is going to have dynamic dependent lists across the whole row for the initial parent choice in the first cell of the row. On Sheet 2, I have a (mock) CSV file that's going to be imported into Excel through our system (we'll worry about that later, not part of this issue). On Sheet 3, I have some formulas that are "running in the background" - meaning, it's a sheet just for putting any formulas on so we can hide that sheet.

Right now, on the CSV, I have a material, let's call it Mat1, that has either a Y or an N for three different properties, those being Wood, Metal, and Concrete, in that order. On Sheet 1, I have a Named Range for all the materials, and on Sheet 3, the formulas are indexing the Wood, Metal, and Concrete (separately) columns, and matching that to the chosen material from the Data Validation list on Sheet 1. Then it takes those Ys and Ns, and changes them to correspond to either of the three types. Then I have another Named Range on those three cells so I can make a list of available options for the chosen material. So if I choose Mat1, the next list will show only Wood available. If I were to choose Mat7, all three would be available

My problem is, this method only works for the first row of Sheet 1, and I need it to be used for as many rows down as the painter needs to put down. So if there's 14 things that need to be estimated, there needs to be 14 rows. Only 8 things, then only 8 rows. I'm thinking something like a button to add new rows would be the way to do this, but right now I want to focus on getting the lists to work on x rows.

I've included my sample file so everyone can take a look: Estimating-test.xls

This also seems like a not-so-pretty way to do this, so I am open to any better ideas out there. I don't know Excel all that well, and don't know VBA at all, and have just been mashing things together as I go along. I'm actually kind of impressed that I've gotten myself this far.

This might sound complicated and convoluted, so if anything is unclear, please ask. This makes sense to me, but I've been in the middle of it for this week so far.

Thanks in advance
0
Comment
Question by:gschmidt1
  • 2
3 Comments
 
LVL 5

Expert Comment

by:rubikru
ID: 33731713
Hello!

I hope I got You right.

See the solution in the file.


Estimating-test.xls
0
 

Author Comment

by:gschmidt1
ID: 33737201
Not quite. I'll try and clarify.

The parent list (column A) is fine, it's the dependent list (column B) that I need help with. If I only have a single row, the whole thing works fine, but I need to be able to expand this down a few rows and still have the same functionality. The dependent list needs to be specific to the choice made in the parent list of that row. So if I choose "Mat3" from the list in A2, the list in B2 will reflect the available options based on the choice in A2.

Now, I run into problems when I try and expand this down to more rows, I am able to retain the functionality of column A since all that is is a Named Range, but what I lose is the functionality for the B column. I need a way to iterate the B column down multiple rows. Right now, the way the sheet works is on Sheet 3, B2 is referencing Sheet1!A2, and in C2:C4, there are formulas that are checking against B2 and adding the appropriate choices to those cells. That same C2:C4 is currently in a Named Range, and that's how the dependent list is filled in on Sheet 1. Because of that reference to Sheet1!A2, it makes the whole thing unable to used on multiple rows, and that's where I'm stuck.

I need a way to be able to add as many rows as needed and maintain the functionality of the dependent list specific to the row it's on.
0
 

Accepted Solution

by:
gschmidt1 earned 0 total points
ID: 33784917
I have it kind of working. I got it to display the menu based of the row of the last modified cell. It's not perfect, but it's something. I guess I'll take it from here.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now