show data dependant on certain columns (2 dif questions)

[Steve]

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

 

[ShaneDevenshire]

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.

 

[ShaneDevenshire]

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.

 

[Steve]

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]

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.

 

[Steve]

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]

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.

 

[Steve]

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

 

[Max]

.. 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.