| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complicated value lookup | Excel Discussion (Misc queries) | |||
| Complicated Stacked Bar Chart? | Charts and Charting in Excel | |||
| Complicated Formulas | Excel Worksheet Functions | |||
| Complicated Formula | Excel Discussion (Misc queries) | |||
| How do you create a complicated pivot table graph? | Charts and Charting in Excel | |||
Linear Mode
