Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel 2007 Dynamic Dependent Data Validation Lists issues

Posted on 2010-09-21
3
Medium Priority
?
822 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Eseutil Hard Recovery is part of exchange tool and ensures Exchange mailbox data recovery when mailbox gets corrupt due to some problem on Exchange server.
Today as you open your Outlook, you witness an error message: “Outlook is using an old copy of your Outlook Data File…”. Probably, Outlook is accessing an old OST file.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

772 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