Solved

Excel 2007 Dynamic Dependent Data Validation Lists issues

Posted on 2010-09-21
3
806 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

832 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