SpreadCE / Spread32

I am trying to build a spreadsheet to record animal behaviour. Each row represents one behaviour (column 2) with supporting data (columns 3,4,5...etc). Each column represents a different type of supporting data (e.g. distance or context). ach entry is time stamped in column 1.

When the observer enters a behaviour in column 2 they need to be directed to a series of cells in the same row to enter further supporting data. Which columns these cells are in depends on the initial entry in column 2 (i.e. the supporting data needed depends on the paerticular behaviour).

The time stamp and inventory threads have been very useful but I am having problems getting a macro to branch off to different commands depending on the initial entry. It works fine very simply:

Auto_Open macro:
=ON.ENTRY("[Focals.xls]Sheet1","DataEntry")
=RETURN()

DataEntry macro:
=ECHO(FALSE) Change in column 1 time stamped in column 2
=IF(COLUMN(CALLER())=2)
=SELECT(OFFSET(ACTIVE.CELL(),0,-1))
=FORMULA(NOW())
=FORMAT.NUMBER("hh:mm:ss")
=SELECT(OFFSET(ACTIVE.CELL(),0,1))
=END.IF()

=IF(ACTIVE.CELL()="gr") If behaviour in column 2 ="gr"
=SELECT(OFFSET(ACTIVE.CELL(),-1,1)) cell in column 3 is selected
=ELSE.IF(COLUMN(CALLER())=3) when cell in column 3 is changed
=SELECT(OFFSET(ACTIVE.CELL(),-1,1)) cell in column 4 is selected
=ELSE.IF(COLUMN(CALLER())=4) when cell in column 4 is changed
=SELECT(OFFSET(ACTIVE.CELL(),0,-2)) cell in column 2 on next line is selected ready for next entry
=END.IF()

=IF(ACTIVE.CELL()="br") As above but if behaviour = "br" data is entered in column 7
=SELECT(OFFSET(ACTIVE.CELL(),-1,5))
=ELSE.IF(COLUMN(CALLER())=7)
=SELECT(OFFSET(ACTIVE.CELL(),0,-5))
=END.IF()
=RETURN()

But when I try to add another behaviour that requires data entry in the same column as any above then it all goes wrong. For example is this IF clause is inserted in the above code:

=IF(ACTIVE.CELL()="po")
=SELECT(OFFSET(ACTIVE.CELL(),-1,1))
=ELSE.IF(COLUMN(CALLER())=3)
=SELECT(OFFSET(ACTIVE.CELL(),-1,2))
=ELSE.IF(COLUMN(CALLER())=5)
=SELECT(OFFSET(ACTIVE.CELL(),0,-3))
=END.IF()

Here when data is entered in column 3 a cell 3 across and one up is selected. I think this is because the two =ELSE.IF(COLUMN(CALLER())=3) commands are both being activated.

Is there a macro function to either pause the macro between data being entered in each column so I can simply use stand alone IF clauses with pauses between each data entry? At the moment each time data is entered the entire macro is run start to finish.

Any help greatly appreciated!

Harry

Hi Harry,

1) Taking your existing macro and converting it back to English, this is what I get:

When any data is entered on sheet 1

---If data was entered in column 2
------put time stamp in column 1 on same row
---End if

---If "gr" is entered in any cell
------move 1 column to the right in the same row
---else if data was entered in column 3
------move to column 4 in the same row
---else if data was entered in column 4
------move to column 2 in the next row
---end if

---if "br" is entered in any cell
------move to column 7 in the same row
---else if data was entered in column 7
------move to column 2 in the next row
---end if

2) I think that what you actually wanted was this:

When any data is entered on sheet 1

---If data was entered in column 2
------put time stamp in column 1 on same row
---End if

---If column 2 contains "gr"
---and data was entered in column 2
------move to column 3 in the same row
---End if

---If column 2 contains "gr"
---and data was entered in column 3
------move to column 4 in the same row
---End if

---If column 2 contains "gr"
---and data was entered in column 4
------move to column 2 in the next row
---End if

---If column 2 contains "br"
---and data was entered in column 2
------move to column 7 in the same row
---End if

---If column 2 contains "br"
---and data was entered in column 7
------move to column 2 in the next row
---End if

3) We can rearrange this to:

When any data is entered on sheet 1

---If data was entered in column 2
------put time stamp in column 1 on same row
---End if

---If column 2 of this row contains "gr"
------If data was entered in column 2
---------move to column 3 in the same row
------Else If data was entered in column 3
---------move to column 4 in the same row
------Else If data was entered in column 4
---------move to column 2 in the next row
------End if
---Else If column 2 of this row contains "br"
------If data was entered in column 2
---------move to column 7 in the same row
------Else If data was entered in column 7
---------move to column 2 in the next row
------End if
---End if

4) Converting this back to macro language we get:

<time stamp part omitted because it works fine>

=IF(OFFSET(CALLER(),0,2-COLUMN(CALLER()))="gr")
---=IF(COLUMN(CALLER())=2)
------=SELECT(OFFSET(CALLER(),-1,1))
---=ELSE.IF(COLUMN(CALLER())=3)
------=SELECT(OFFSET(CALLER(),-1,1))
---=ELSE.IF(COLUMN(CALLER())=4)
------=SELECT(OFFSET(CALLER(),0,-2))
---=END.IF()
=ELSE.IF(OFFSET(CALLER(),0,2-COLUMN(CALLER()))="br")
---=IF(COLUMN(CALLER())=2)
------=SELECT(OFFSET(CALLER(),-1,5))
---=ELSE.IF(COLUMN(CALLER())=7)
------=SELECT(OFFSET(CALLER(),0,-5))
---=END.IF()
=END.IF()

The indentation here is just for readability, you would actually need to enter the formulas without any spaces at the beginning.

Hi Stephen,
Thank you that worked great.

Cheers,

Harry