This is more complicated than it looks.
 

Here is what I have.
I have a Master List with 3 colums The first column labeled location
has a pull down menu with about 20 different options. The middle column
labeled issues is just for text entry. The third column labeled
contractor is another pull down menu with 18 options on it. I also have
18 other sheets within the workbook that correspond to the third column
pulldown menu.

Heres what I need.
I would like to be able to enter info from columns one and two and be
able to choose an option from column three and the info from columns
one and two go to the corresponding sheet.

I dont know if this is possible without writing a hole new program but
if it is any help would be greatly appreciated..


--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052

This is more complicated than it looks.
 

If anybody can help....


+-------------------------------------------------------------------+
|Filename: Sebastian Punchlist.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5003 |
+-------------------------------------------------------------------+

--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052

This is more complicated than it looks.
 

Get kind of creative with CELL, INDEX, MATCH, ISNA, OFFSET and IF...

Oh, ok, I got kind of creative with them and came up with something that I
believe will do what you're hoping for. Not perfect, will leave gaps in rows
on the sheets the data is transferred to, but using Data | Auto Filter and
choosing [Non Blanks] will close up those gaps for better appearance.

I borrowed some code from Rodney Powell's site to a self-maintaining list of
sheet names to use as the data validation list and set the list up with
CELL() formula to determine the names of the sheets in the workbook. So the
list will automatically update sheet names even if a user changes them. You
only have to mess with the list when you first set it up and when you add new
sheets to the workbook.

I tried to explain my formulas for pulling the data from the main source
data sheet to the others, but the two formulas used there fall into the
Really Ugly category and I probably didn't do a good job of it.

Anyhow, the workbook is available he
http://www.jlathamsite.com/uploads/
CopyToOtherSheetsByChoice.xls
(that should all be one line address). There is no macro code in the
workbook, it's all done with formulas. My contact info is on one of the
worksheets.



"dollarbill79" wrote:


Here is what I have.
I have a Master List with 3 colums The first column labeled location
has a pull down menu with about 20 different options. The middle column
labeled issues is just for text entry. The third column labeled
contractor is another pull down menu with 18 options on it. I also have
18 other sheets within the workbook that correspond to the third column
pulldown menu.

Heres what I need.
I would like to be able to enter info from columns one and two and be
able to choose an option from column three and the info from columns
one and two go to the corresponding sheet.

I dont know if this is possible without writing a hole new program but
if it is any help would be greatly appreciated..


--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052

This is more complicated than it looks.
 

Went and grabbed the sample file you uploaded and modified it and have put
the modification he
http://www.jlathamsite.com/uploads/S...nchlist_V2.xls

Explanations from the previous file apply, I've added some short comments
into this workbook but they refer to the other one also, so you may want both
to make sense of it all.

Hope this helps.

"dollarbill79" wrote:


Here is what I have.
I have a Master List with 3 colums The first column labeled location
has a pull down menu with about 20 different options. The middle column
labeled issues is just for text entry. The third column labeled
contractor is another pull down menu with 18 options on it. I also have
18 other sheets within the workbook that correspond to the third column
pulldown menu.

Heres what I need.
I would like to be able to enter info from columns one and two and be
able to choose an option from column three and the info from columns
one and two go to the corresponding sheet.

I dont know if this is possible without writing a hole new program but
if it is any help would be greatly appreciated..


--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052

This is more complicated than it looks.
 

Hey thanks for all the help....this is going to make my job even
easier.....


--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052

This is more complicated than it looks.
 

Hey thanks for all the help....this is going to make my job even
easier.....


--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052

This is more complicated than it looks.
 

Hey thanks for all the help....this is going to make my job even
easier.....


--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052

This is more complicated than it looks.
 

Hey thanks for all the help....this is going to make my job even
easier.....


--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052

This is more complicated than it looks.
 

=You're Welcome * 4
What I liked about it all once it was done is that it's almost maintenance
free. Need another area? just copy and rename any of the sheets and extend
the formula for the data validation list on the Master sheet and you're done!

"dollarbill79" wrote:


Hey thanks for all the help....this is going to make my job even
easier.....


--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052

This is more complicated than it looks.
 

I got to using the spreadsheet and for the most part it works fantastic
except one thing. if i have more than one row with the same contractor
(column 3) it only transfers the first item on the list. i started to
fiddle with the formula but it got me frustrated. i attatched my latest
copy.


+-------------------------------------------------------------------+
|Filename: SebastianPunchlist_V2 with bugs.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5012 |
+-------------------------------------------------------------------+

--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052

This is more complicated than it looks.
 

I'll take a look at what you've uploaded and see if it is fixable.

"dollarbill79" wrote:


I got to using the spreadsheet and for the most part it works fantastic
except one thing. if i have more than one row with the same contractor
(column 3) it only transfers the first item on the list. i started to
fiddle with the formula but it got me frustrated. i attatched my latest
copy.


+-------------------------------------------------------------------+
|Filename: SebastianPunchlist_V2 with bugs.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5012 |
+-------------------------------------------------------------------+

--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052

This is more complicated than it looks.
 

Fixed it.
http://www.jlathamsite.com/uploads/S...nchlist_v3.xls

I got too focused on trying to close up gaps in the other version. And even
though it didn't do that, I thought it was picking up what it needed to.
Obviously not, and obviously because I didn't test it enough.

I simplified the formulas on the individual sheets - they basically now have
a one-to-one relationship with rows on the Master sheet. What it boils down
to is that they echo whatever is on a row on the master sheet on the same row
on the other sheets when the choice made in Column G on the Master sheet
matches the entry in A$3 on each of the other sheets. You can still filter
by non-blank rows to close up the gaps for neatness.

Let me know if there turns out to be something else I've overlooked.

"dollarbill79" wrote:


I got to using the spreadsheet and for the most part it works fantastic
except one thing. if i have more than one row with the same contractor
(column 3) it only transfers the first item on the list. i started to
fiddle with the formula but it got me frustrated. i attatched my latest
copy.


+-------------------------------------------------------------------+
|Filename: SebastianPunchlist_V2 with bugs.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5012 |
+-------------------------------------------------------------------+

--
dollarbill79
------------------------------------------------------------------------
dollarbill79's Profile: http://www.excelforum.com/member.php...o&userid=36212
View this thread: http://www.excelforum.com/showthread...hreadid=560052