Conditional formatting... hopefully!!

[RLSaSSy]

Hello everyone,
I am stuck and need your expertise!!

The main worksheet is the pre-screening worksheet where we do the first input. Here is what I’m trying to do:

1. In the pre-screening worksheet, there is a column titled ‘Screened in/out’.
2. If there is an “IN”, I want to replicate most of the data for that row into the assessment worksheet.
3. The data replicated would be the following columns:
a. Organization Name
b. CGSC Number
c. Province
d. Org type
e. National Y/N
f. Location of Activities
g. Funding Amount requested

I’ve tried to figure this one out but I can’t. If you can help, that would be greatly appreciated.

[Pete_UK]

Can you just put some dummy data in your example file (about 5 rows' worth), so we can see how you want it to be split up?

Pete

EDIT: It won't be conditional formatting !!

[Fotis1991]

Hi

Your attachment workbook, is empty. No data inside!

[RLSaSSy]

sorry guys... here you go!!

[Pete_UK]

I've set this up for you using formulae in the attached workbook, by using a helper column (J) in the Pre_screening sheet, a new column A in the Assessment sheet (which could be hidden), and formulae in columns C to H of that sheet - this will copy the data across automatically where "Screened IN/OUT" is set to IN, as in your 3 examples.

HOWEVER, I'm not sure that this formula-based approach is the most suitable for what I expect you to do next.

It looks like you will be adding more data to the Assessment sheet, but you must realise that the transfer of data to that sheet is dynamic, depending on the settings of the Screened field. At the moment, records on rows 3, 5 and 6 are copied across and appear as records 1, 2 and 3 in the second sheet. BUT, if you were subsequently to change the record on row 4, for example, to IN, then that would become record 2 in the second sheet, and those from rows 5 and 6 would become records 3 and 4. So, if you had put any extra data against the second record, for example, and then changed the status for the row-4 record, then it would become mis-aligned.

If you do intend to use the workbook in this way, then I would suggest you need a macro approach, whereby a record is written to the Assessment sheet once only when its status is changed to IN, and that its position in the Assessment sheet is never changed. I'm not really up to putting such a macro together, though.

Hope this helps.

Pete

[Fotis1991]

You can do this, without limits, using this Array(Control+Shift+Enter(Not just Enter)formula.

Please Login or Register  to view this content.

Is this, works for you?