Linking cells between different tabs

  • james.me.robinson
  • Offline
More
8 years 7 months ago #5648 by james.me.robinson
Linking cells between different tabs was created by james.me.robinson
I would really like to get iDoceo to collate assessment information in one tab so that I can see the overall progress of the pupil in all assessment areas.

We have separate tabs for each unit assessment which are linked to our assessment criteria. When I tick these cells, I would like them to link to the overall APP sheet so that I can see which APP criteria have been met. The difficult thing is that several different assessments will link to the same APP criteria and so I need it to look at all instances of a criteria and tick if any of them are ticked.

E.g. In a fusion unit a pupil is able to add loops (T1). In a later unit they can also add loops, also T1. I would like iDoceo to be able to look at all the tabs, and see if T1 has been ticked anywhere.

I have added some photos if that helps.
Attachments:

Please Log in or Create an account to join the conversation.

More
8 years 7 months ago - 8 years 7 months ago #5649 by bert
Replied by bert on topic Linking cells between different tabs
Hello James,
Have you tried the linked columns feature? You can view/edit the same columns in different tabs at the same time.
Here is the article explaining the details
Last edit: 8 years 7 months ago by bert.

Please Log in or Create an account to join the conversation.

  • james.me.robinson
  • Offline
More
8 years 7 months ago #5650 by james.me.robinson
Replied by james.me.robinson on topic Linking cells between different tabs
Hi Bert,
The linked columns won't work I don't think as there are several columns that need to link to one column in the APP sheet. It needs to be more of an IF function I think so that if a certain cell is ticked in any of the relevant columns then a tick appears in the APP sheet.

Does that make sense?

Please Log in or Create an account to join the conversation.

More
8 years 7 months ago #5651 by bert
Replied by bert on topic Linking cells between different tabs
Absolutely. It requires some additional configuration on your side. If you are in the mood, here are the steps.

You need to assign a numeric value to check marks first so that iDoceo knows how to work with the IF condition in your formula.

This isn't too difficult, the steps are explained here in detail
In short:
You must configure a grade type (let's call it 'checkmarks') and assign a 1 to the check mark icon. you only have to do this once.

Assign this 'checkmarks' grade type to your columns.

You can either do this with a single column: Double tap on a header > Grade type
or in bulk: Tools > Columns > Select columns > Edit > Grade type

You will now be able to configure a Formula (add column > calculation > formula) with the IF functions you need. The result will be numeric but you can show that result automatically as an icon as well. Let's focus on the formula first.

If a tick on any of the relevant columns (COLUMN1, COLUMN2 in example) would yield a tick (value 1) in the app sheet the formula would look like this:

IF( MAX(COLUMN1,COLUMN2,COLUMN3)>=1, 1, 0)

Please Log in or Create an account to join the conversation.

  • james.me.robinson
  • Offline
More
8 years 5 months ago #5875 by james.me.robinson
Replied by james.me.robinson on topic Linking cells between different tabs
Thanks for this Bert. I'm in the process of editing it all, but it seems to be working. Hurray!

Just another query.

Rather than displaying a number, can I get it to display a tick when the value is one or above and nothing when it is zero? Or could I have a range of symbols so I can show if the value is 2,3 etc. so I can show the strength of meeting the criteria?

Thanks for all your help.

Please Log in or Create an account to join the conversation.

More
8 years 5 months ago #5876 by bert
Replied by bert on topic Linking cells between different tabs
Yes. You will find the details explained here.
www.idoceo.net/index.php/en/howto/calcul...lculation-as-an-icon
Focus on getting the numeric result right first (0,1,2,3) and the icon conversion will be only a couple of steps away.

Please Log in or Create an account to join the conversation.

  • james.me.robinson
  • Offline
More
8 years 5 months ago #5885 by james.me.robinson
Replied by james.me.robinson on topic Linking cells between different tabs
Brilliant thanks Bert.

One more problem, sorry.

I've created the whole iDoceo workbook and shared with staff without the overall APP sheet, but now need to copy the overall sheet to the other teachers' class workbooks so that they can have the overview. When I copy it across, the formulas don't seem to be working, even though the tabs and columns are the same as the workbook I have created it on. Is there a quick way to ensure the formulas update and attach themselves to the tabs and columns in their workbooks without going through each one individually?

I have attached a draft version win the formulas working on the first few columns of the APP sheet.
Attachments:

Please Log in or Create an account to join the conversation.

More
8 years 5 months ago #5886 by bert
Replied by bert on topic Linking cells between different tabs
Hi James,
I'm afraid you can't transfer that single APP tab withs calculations after importing a template. The original formula and its composition is based on a unique internal ID not on columns names.

Please Log in or Create an account to join the conversation.

  • james.me.robinson
  • Offline
More
8 years 5 months ago #5898 by james.me.robinson
Replied by james.me.robinson on topic Linking cells between different tabs
Hi Bert, that is a shame. I'll have to think of a way around it.
If they already have columns in a class book with videos etc. is there an easy way to copy this to the new template?

Please Log in or Create an account to join the conversation.