Loading...
 
Skip to main content

Features / Usability

Forum List Topic List

Forums » Features / Usability » tiki sheets cross sheet referencing
 
Thread actions
Print this page
  • Print all pages

    • tiki sheets cross sheet referencing

    Posted by seromine posts: 130

    Hello!

    Working as a volunteer for a library who has tikiwiki installed (version 6.4 I believe). They would like to start using tiki sheets to do some of there monthly statistics gathering.

    I am having a hard time figuring out the formatting for cross sheet formatting. For example, if i have a tiki sheet called Jan Stats and a tiki sheet called Total stats, what is the format to reference a cell in Jan stats sheet from a cell in Total stats sheet?

    Reads: 2764
    Posted by robertplummer posts: 143 United States

    I'm Robert Plummer, the creator of jQuery.sheet, which is the plugin used in Tiki for sheets. The sheets need to be added to each other, one as a parent and the other as a child. I strongly urge you to upgrade to the latest Tiki 8.3 to use them. But a reference to another sheet can be done like this:

    Copy to clipboard
    =SHEET2!A1


    or if you wanted to do sum:

    Copy to clipboard
    =SUM(SHEET2!A1:B2)

    Posted by seromine posts: 130

    Thanks for the excellent information. I am at the library right now trying out your recommendations.

    Unfortunately we are using Tikiwiki 6.4 LTS, trying to keep with the LTS version. Can I ask why you were very adamant about making sure we had version 8.3? Was it for stability reasons, or maybe kore feature rick in 8.3?

    Would there be an angle for us to just update the jquery.sheet portion in 6.6. do you think?


    Posted by seromine posts: 130

    Still wrestling with this

    When using the above, I get a message such as

    1. VALUE!: undefined


    Any ideas on how to troubleshoot? Is the =sheet1!A1 reference the actual name of the sheet, or some type of sheet id?

    Does the fact that if I try and use the cell range, I receive the following?

    =undefined(B4:B6)=undefined(B4:B6)

    Would the "undefined" above normally be a sheet name if everything was working?

    Is this the reason i should go to 8.3?


    Posted by robertplummer posts: 143 United States

    When Tiki branches we do our best to ensure that the branch stabalizes, but it is hard to line everything for 3rd party packages. This is one of those very odd cases, and 8.3 (and more specifically jQuery.sheet) has a totally new system built in for ensuring that those formulas work. The correct syntax is upper-case selectors and functions...

    Copy to clipboard
    =SHEET2!A1


    should work fine to look at the second sheet attached to the current sheet.

    Copy to clipboard
    =sheet2!a1


    I don't think will.

    Also, some of the syntax was in flux at that time, you might try this:

    Copy to clipboard
    =SHEET2:A1


    Notize the ":" rather than the "!" , which we changed to later to be more compliant with spreadsheet standards.