Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
show data dependant on certain columns (2 dif questions)
Not sure the title coverts exactly what i want.
I have a workbook set up to monitor interviews done by staff. The system only records the customers on an outcome within the target window I have an input sheet and 12 monthly sheets. customers are moved from the input sheet to the month once there is an outcome Question 1 Certain fields are protected, can staff cut from the sheet and paste into another while it is protected, if not any suggestions how they move Question 2 Some of the outcomes require further follow up so i want to generate a list based on certain outcomes. So the FTA column in each month has a "yes" i want a summary page of all of these so i want to say something like if FTA column is YES then take info from column a,b,c and put it in the summary page hopefully this makes sense |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
show data dependant on certain columns (2 dif questions)
Hi,
You can't copy/cut and paste while protection is on. You have a number of options: 1. Rather than copy use formulas which are triggered by criteria on the originating sheet 2. Create a macro approach for handling the copy/cut and paste - a macro can move data even if the sheets are protected if you enable that feature. 3. You can remove protection during the copy and paste step and then turn it back on afterwards. -- cheers, Shane Devenshire "Steve" wrote: Not sure the title coverts exactly what i want. I have a workbook set up to monitor interviews done by staff. The system only records the customers on an outcome within the target window I have an input sheet and 12 monthly sheets. customers are moved from the input sheet to the month once there is an outcome Question 1 Certain fields are protected, can staff cut from the sheet and paste into another while it is protected, if not any suggestions how they move Question 2 Some of the outcomes require further follow up so i want to generate a list based on certain outcomes. So the FTA column in each month has a "yes" i want a summary page of all of these so i want to say something like if FTA column is YES then take info from column a,b,c and put it in the summary page hopefully this makes sense |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
show data dependant on certain columns (2 dif questions)
Sorry I sent my last email before I had completed it:
4. You can unlock the cells where you want the user to be able to paste. 5. You might choose the Tools, Protection, Allow Users to edit ranges option - where you can allow certain users to edit certain ranges depending on passwords. -- Thanks, Shane Devenshire "Steve" wrote: Not sure the title coverts exactly what i want. I have a workbook set up to monitor interviews done by staff. The system only records the customers on an outcome within the target window I have an input sheet and 12 monthly sheets. customers are moved from the input sheet to the month once there is an outcome Question 1 Certain fields are protected, can staff cut from the sheet and paste into another while it is protected, if not any suggestions how they move Question 2 Some of the outcomes require further follow up so i want to generate a list based on certain outcomes. So the FTA column in each month has a "yes" i want a summary page of all of these so i want to say something like if FTA column is YES then take info from column a,b,c and put it in the summary page hopefully this makes sense |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
show data dependant on certain columns (2 dif questions)
how about the list creation
is there a way of looking at one column and getting it to compile a list of other info on the row? "ShaneDevenshire" wrote Sorry I sent my last email before I had completed it: 4. You can unlock the cells where you want the user to be able to paste. 5. You might choose the Tools, Protection, Allow Users to edit ranges option - where you can allow certain users to edit certain ranges depending on passwords. -- Thanks, Shane Devenshire "Steve" wrote: Not sure the title coverts exactly what i want. I have a workbook set up to monitor interviews done by staff. The system only records the customers on an outcome within the target window I have an input sheet and 12 monthly sheets. customers are moved from the input sheet to the month once there is an outcome Question 1 Certain fields are protected, can staff cut from the sheet and paste into another while it is protected, if not any suggestions how they move Question 2 Some of the outcomes require further follow up so i want to generate a list based on certain outcomes. So the FTA column in each month has a "yes" i want a summary page of all of these so i want to say something like if FTA column is YES then take info from column a,b,c and put it in the summary page hopefully this makes sense |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
show data dependant on certain columns (2 dif questions)
Hi,
I'm not clear on the question, so this is just a guess - you can create a Data, Validation drop down which uses a list. To the right of that you might add a number of VLOOKUP functions which use the results to bring back the rest of the data. You would need to be more specific to get a less general suggestion - for example, show us some dummy data. -- Thanks, Shane Devenshire "Steve" wrote: how about the list creation is there a way of looking at one column and getting it to compile a list of other info on the row? "ShaneDevenshire" wrote Sorry I sent my last email before I had completed it: 4. You can unlock the cells where you want the user to be able to paste. 5. You might choose the Tools, Protection, Allow Users to edit ranges option - where you can allow certain users to edit certain ranges depending on passwords. -- Thanks, Shane Devenshire "Steve" wrote: Not sure the title coverts exactly what i want. I have a workbook set up to monitor interviews done by staff. The system only records the customers on an outcome within the target window I have an input sheet and 12 monthly sheets. customers are moved from the input sheet to the month once there is an outcome Question 1 Certain fields are protected, can staff cut from the sheet and paste into another while it is protected, if not any suggestions how they move Question 2 Some of the outcomes require further follow up so i want to generate a list based on certain outcomes. So the FTA column in each month has a "yes" i want a summary page of all of these so i want to say something like if FTA column is YES then take info from column a,b,c and put it in the summary page hopefully this makes sense |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
show data dependant on certain columns (2 dif questions)
ok a bit more specific
13 worksheets First 12 are months 13 is a summary I want the summary to look at Col R in each month and if it has a date in then pull col A & B info (name) through to the summary sheet I am going to have different areas on the summary for each month so the list would just cover one month at a time "ShaneDevenshire" wrote: Hi, I'm not clear on the question, so this is just a guess - you can create a Data, Validation drop down which uses a list. To the right of that you might add a number of VLOOKUP functions which use the results to bring back the rest of the data. You would need to be more specific to get a less general suggestion - for example, show us some dummy data. -- Thanks, Shane Devenshire "Steve" wrote: how about the list creation is there a way of looking at one column and getting it to compile a list of other info on the row? "ShaneDevenshire" wrote Sorry I sent my last email before I had completed it: 4. You can unlock the cells where you want the user to be able to paste. 5. You might choose the Tools, Protection, Allow Users to edit ranges option - where you can allow certain users to edit certain ranges depending on passwords. -- Thanks, Shane Devenshire "Steve" wrote: Not sure the title coverts exactly what i want. I have a workbook set up to monitor interviews done by staff. The system only records the customers on an outcome within the target window I have an input sheet and 12 monthly sheets. customers are moved from the input sheet to the month once there is an outcome Question 1 Certain fields are protected, can staff cut from the sheet and paste into another while it is protected, if not any suggestions how they move Question 2 Some of the outcomes require further follow up so i want to generate a list based on certain outcomes. So the FTA column in each month has a "yes" i want a summary page of all of these so i want to say something like if FTA column is YES then take info from column a,b,c and put it in the summary page hopefully this makes sense |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
show data dependant on certain columns (2 dif questions)
Hi,
Assume that the data is solid, that means that on the month sheets there are no blank rows between data. Assume data starts on row 2 on each of the month sheets. Assume there are ten possible rows of data on any one month sheet. On the summary sheet assume that the first months data will go into rows A2:B11 and assume that the second months data will go into the range A15:B24. Assume that the dates are in cells R2:R11. Assume that column R is empty if there are no dates (that is assume that the date field is only filled if it is filled with a date). Assume that the date is not relevant. Assume that the sheets are named Jan, Feb, Mar,.... Lots of assumptions as you see. In cell A2 enter a formula such as =IF(Jan!$R2<"",Jan!A2,"") This formula can be copied to the range A2:B11 of the Summary sheet. -- Cheers, Shane Devenshire "Steve" wrote: ok a bit more specific 13 worksheets First 12 are months 13 is a summary I want the summary to look at Col R in each month and if it has a date in then pull col A & B info (name) through to the summary sheet I am going to have different areas on the summary for each month so the list would just cover one month at a time "ShaneDevenshire" wrote: Hi, I'm not clear on the question, so this is just a guess - you can create a Data, Validation drop down which uses a list. To the right of that you might add a number of VLOOKUP functions which use the results to bring back the rest of the data. You would need to be more specific to get a less general suggestion - for example, show us some dummy data. -- Thanks, Shane Devenshire "Steve" wrote: how about the list creation is there a way of looking at one column and getting it to compile a list of other info on the row? "ShaneDevenshire" wrote Sorry I sent my last email before I had completed it: 4. You can unlock the cells where you want the user to be able to paste. 5. You might choose the Tools, Protection, Allow Users to edit ranges option - where you can allow certain users to edit certain ranges depending on passwords. -- Thanks, Shane Devenshire "Steve" wrote: Not sure the title coverts exactly what i want. I have a workbook set up to monitor interviews done by staff. The system only records the customers on an outcome within the target window I have an input sheet and 12 monthly sheets. customers are moved from the input sheet to the month once there is an outcome Question 1 Certain fields are protected, can staff cut from the sheet and paste into another while it is protected, if not any suggestions how they move Question 2 Some of the outcomes require further follow up so i want to generate a list based on certain outcomes. So the FTA column in each month has a "yes" i want a summary page of all of these so i want to say something like if FTA column is YES then take info from column a,b,c and put it in the summary page hopefully this makes sense |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
show data dependant on certain columns (2 dif questions)
That works perfectly ta very much
however (lol) The list is equal to the original i mean the enteries appear as far down the page as they do on the month. Is there any way on the list i can pull them to the top of the page. Also ihave used a range in the calc you sent "ShaneDevenshire" wrote: Hi, Assume that the data is solid, that means that on the month sheets there are no blank rows between data. Assume data starts on row 2 on each of the month sheets. Assume there are ten possible rows of data on any one month sheet. On the summary sheet assume that the first months data will go into rows A2:B11 and assume that the second months data will go into the range A15:B24. Assume that the dates are in cells R2:R11. Assume that column R is empty if there are no dates (that is assume that the date field is only filled if it is filled with a date). Assume that the date is not relevant. Assume that the sheets are named Jan, Feb, Mar,.... Lots of assumptions as you see. In cell A2 enter a formula such as =IF(Jan!$R2<"",Jan!A2,"") This formula can be copied to the range A2:B11 of the Summary sheet. -- Cheers, Shane Devenshire "Steve" wrote: ok a bit more specific 13 worksheets First 12 are months 13 is a summary I want the summary to look at Col R in each month and if it has a date in then pull col A & B info (name) through to the summary sheet I am going to have different areas on the summary for each month so the list would just cover one month at a time "ShaneDevenshire" wrote: Hi, I'm not clear on the question, so this is just a guess - you can create a Data, Validation drop down which uses a list. To the right of that you might add a number of VLOOKUP functions which use the results to bring back the rest of the data. You would need to be more specific to get a less general suggestion - for example, show us some dummy data. -- Thanks, Shane Devenshire "Steve" wrote: how about the list creation is there a way of looking at one column and getting it to compile a list of other info on the row? "ShaneDevenshire" wrote Sorry I sent my last email before I had completed it: 4. You can unlock the cells where you want the user to be able to paste. 5. You might choose the Tools, Protection, Allow Users to edit ranges option - where you can allow certain users to edit certain ranges depending on passwords. -- Thanks, Shane Devenshire "Steve" wrote: Not sure the title coverts exactly what i want. I have a workbook set up to monitor interviews done by staff. The system only records the customers on an outcome within the target window I have an input sheet and 12 monthly sheets. customers are moved from the input sheet to the month once there is an outcome Question 1 Certain fields are protected, can staff cut from the sheet and paste into another while it is protected, if not any suggestions how they move Question 2 Some of the outcomes require further follow up so i want to generate a list based on certain outcomes. So the FTA column in each month has a "yes" i want a summary page of all of these so i want to say something like if FTA column is YES then take info from column a,b,c and put it in the summary page hopefully this makes sense |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
show data dependant on certain columns (2 dif questions)
"Steve" wrote:
.. Is there any way on the list i can pull them to the top of the page. One way to achieve it using non-array formulas In the summary sheet, Assume cols A to C will be the area for extracts from source sheet: Jan Put in A2: =IF(Jan!R2="","",ROW()) Leave A1 blank Put in B2: =IF(ROW(A1)COUNT($A:$A),"",INDEX(Jan!A:A,SMALL($A :$A,ROW(A1)))) Copy B2 to C2. Select A2:C2, copy down to cover the max expected extent of source data. Hide away col A. Cols B and C will return the required results from cols A and B in the source sheet: Jan, all neatly bunched at the top. Repeat likewise the construct for extracts from the other months' (Feb, Mar ....) source sheets. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show data in columns | Excel Discussion (Misc queries) | |||
Use mixed references to show row data as columns in another book | Excel Discussion (Misc queries) | |||
Show data dependant on selection from drop down box | Excel Worksheet Functions | |||
show sereral columns of data field in pivot table | New Users to Excel | |||
pivot tables-drop data in, how to make it show as columns instead | Excel Discussion (Misc queries) |