need to iterate, or something...

hello.
can you help?

I have a worksheet with 3 columbs and 10 rows like so:

ACTION.....workbook.....complete? Y/N
text num y/n

on another sheet, which is an overview i have this in one of the cells:

=IF(AND(Rhiannon!B14="A",Rhiannon!C14=1),IF(NOT(ISBLANK(Rhiannon!D14)),IF(Rhiannon!D14="Y","Complete","Assessed"),"Workshop complete Y/N is blank"),CONCATENATE(SUM(AA6-TODAY())," days to deadline"))

so, if in any of the 10 rows under the action columb there is an 'A' and a '1' under the workbook column then i print either 'Assessed' or 'Complete' depending upon the complete? Y/N column.

If there isn't an 'A' in any of the rows in the ACTION columb, i print the time remaining to the deadline (AA6 is the deadline date).

I have this working for checking one row, but can't think how to do it for all 10.

I've tried VBA, but it's been ages since i've touched it.
Is there a way of doing this in formula?

thankyou for taking the time to look at my query.

okay i've nearly got it!
just got one problem which i can't figure out!
maybe i'm missing something silly that you lot won't.

I've got this function in a module:

Please Login or Register  to view this content.

then i've got this in cell AA6:

Please Login or Register  to view this content.

but it's returning a #VALUE! error when it calls that function!

Why?

hmmm... i am familiar with VB not VBA. It feels strange!

okay, so i've turned it into a sub like so:

Please Login or Register  to view this content.

but now it's showing a #NAME! error. does that mean it can't recognise the sub?

You can't invoke a sub from the worksheet; it has to be called from another sub or function. And if a function invoked from the worksheet calls a sub, that sub (like the function) cannot modify the spreadsheet.

You need to invoke the sub by running it from the Alt+F8 dialog box, or from a control on a form, or a control from the Forms toolbar or the Control Toolbox, or an autoshape assigned to the macro.

so i can call a function from a worksheet formula which in turn calls a sub which can modify the worksheet?

i've abandoned using vba, i've stuck it all in a formula:

Please Login or Register  to view this content.

looks horrid, i know.

I was only playing around with vba so i didn't have to type i all 10 different names into each 5 formulas (5 workbooks each), but it's probably quicker than getting my head around some of the foreign concepts.

thanks a lot people, you're very helpful

so i can call a function from a worksheet formula which in turn calls a sub which can modify the worksheet?

No; again,

And if a function invoked from the worksheet calls a sub, that sub (like the function) cannot modify the spreadsheet.