Databases
StackOverflow2013
Postsdbo
POUsing cell value as reference to sheet in formulas
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing cell value as reference to sheet in formulas
    primarykey
    data
    text
    <p>I have a spreadsheet with 3 sheets. Two have a bunch of similar data and are called "2012" and "2011". The last sheet does comparisons between the data.</p> <p>To be able to choose year, I'm using a cell where i can I can write either "2011" or "2012" (D1). The formulas then use the INDIRECT formula to include this cell as part of the reference. </p> <pre><code>INDIRECT(CHAR(39)&amp;$D$1&amp;CHAR(39)&amp;"!F:F") </code></pre> <p>This is not a pretty solution and makes the formula quite long and complex.</p> <pre><code>=IFERROR(SUM(FILTER( INDIRECT(CHAR(39)&amp;$D$1&amp;CHAR(39)&amp;"!M:M") ; (INDIRECT(CHAR(39)&amp;$D$1&amp;CHAR(39)&amp;"!B:B")=$A4)+(INDIRECT(CHAR(39)&amp;$D$1&amp;CHAR(39)&amp;"!B:B")=$A5)+(INDIRECT(CHAR(39)&amp;$D$1&amp;CHAR(39)&amp;"!B:B")=$A6)+(INDIRECT(CHAR(39)&amp;$D$1&amp;CHAR(39)&amp;"!B:B")=$A7)+(INDIRECT(CHAR(39)&amp;$D$1&amp;CHAR(39)&amp;"!B:B")=$A8); MONTH(INDIRECT(CHAR(39)&amp;$D$1&amp;CHAR(39)&amp;"!D:D"))=$B$1 ; INDIRECT(CHAR(39)&amp;$D$1&amp;CHAR(39)&amp;"!F:F")=D$3));0) </code></pre> <p>Question 1: Is there a better way of doing this? I've tried to create a separate spreadsheet for the calculations sheet and importing (IMPORTRANGE) the data from the two sheets together on one sheet with VMERGE (custom function from the script gallery), but there is quite a lot og of data in these two sheets and the import takes a long time. Any changes (like changing year) also take a long time to recalculate.</p> <p><strong>EDIT: Please disregard Q2 as I found my own (stupid) error. Forgot to put the custom function inside an INDIRECT formula inside the MONTH formula. Q1 still stands.</strong> </p> <p>Question 2: As a partial solution, i'm trying to write a custom function that does the INDIRECT-stuff in a neater and more readable way. Like RANGE(D1, "A:A"). The function so far looks like this:</p> <pre><code>function range(V1,V2) { return "'" + V1 + "'!" + V2; } </code></pre> <p>But - while it works with:</p> <pre><code>=IFERROR(SUM(FILTER( INDIRECT(RANGE(D1;"B:B")) ; INDIRECT(RANGE(D1;"A:A"))=A4 ));0) </code></pre> <p>It stops working when I throw in a date-variable:</p> <pre><code>=IFERROR(SUM(FILTER( INDIRECT(RANGE(D1;"B:B")) ; INDIRECT(RANGE(D1;"A:A"))=A4 ; MONTH(RANGE(D1;"C:C"))=$B$1 ));0) </code></pre> <p>Any help would be appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USThEvensen
    1. USThEvensen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing cell value as reference to sheet in formulas
      1. This table or related slice is empty.
      1. VTUpMod
    1. COif, as you mentioned, there is "quite a lot of data in these two sheets" I would think custom functions are probably not the most effective approach since they are quite slow... Have you tried to figure out if your data comparisons could be made in javaScript inside a function using data arrays ?
      singulars
      1. POUsing cell value as reference to sheet in formulas
      1. USSerge insas
    2. COAs my custom function does not handle any data-manipulations (as far as I know), I thought that the speed of the custom function would be acceptable. In all essence it only provides the INDIRECT formula with the variable CHAR(39)&$D$1&CHAR(39)&"!A:A", but in a neater way (for better readability). Please correct me if I'm wrong.
      singulars
      1. POUsing cell value as reference to sheet in formulas
      1. USThEvensen
    3. COThe custom function _is_ actually having an impact on speed. Thanks for the tip. I've solved it with DSUM as hinted by the answer below.
      singulars
      1. POUsing cell value as reference to sheet in formulas
      1. USThEvensen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload