Yes, by using the INDIRECT function:

A1: Sales Report - WB 10.06

Then you can use:

=INDIRECT("'"&A1&"'!B3")

perhaps try:

={DataSheet!A1:A; DataSheet2!A1:A}

You should use this:

cell.setFormula("'" + name + "'!" + "A1");

You have:

yes  1
no   2
no   3
yes  4
no   5

You want (on another sheet):

1
4

Easiest solution but with some manual operation: Add a filter to the main sheet Filter for yes answers Copy and paste to the new sheet

Scripting: If the data changes a lot and you need to do this often you can record a macro using Apps Script (Google is your friend). This is the equivalent to VBA in Excel.

Using formulas: "If true then copy" as a FORMULA in a cell implies that the cell with the formula must change the value in another cell. This is counter to the way a spreadsheet works. Rather imagine you are entering a formula into the cell that you want to copy to. This formula can look to any cell and reference its value (not copy). A basic solution:

=IF(A1 = "yes", B1, IF(A2 = "yes", B2, IF(A3 = "yes", A3, "")))

There are of course several problems with this approach. It requires long nested IF statements (or IFS function) and you have to type the IF condition and result for every row in your dataset. Worst still the next cell has no idea how far down the previous cell "looked". To solve this you need to use a match or lookup search function that can return the n'th yes result:

n  value
1  *return 1st yes result
2  *return 2nd yes result

Now if you are familiar with VLOOKUP you'll know it will only return 1st result. To get this right though you need some ARRAY FORMULA and follow this master piece https://infoinspired.com/google-docs/spreadsheet/find-nth-occurrence-in-google-sheets/

There is luckily also the poor man's n'th lookup - if you have a static or single lookup value like "yes". Add a column to the left of your original data. You'll want to count the number of "yes" occurrences to become a lookup index for your second sheet:

Count  Condition    Value
1      yes          1
1      no           2
1      no           3
2      yes          4
2      no           5

This formula in A2 will look like this:

=IF(B2 = "yes", IF(A1 = "Count", 1, A1 + 1), IF(A1 = "Count", 0, A1))

In your other sheet put (in A1) and copy down:

=VLOOKUP(ROW(), Sheet1!A1:C1000, 3, FALSE)

Please try:

 =indirect(E26&"!"&$AV$6)

Indirect is able to read a string as a cell reference. Within the parentheses three elements are concatenated: the content of E26 (which could be as here a sheet named 17.02.2014), the required "!" and the content of AV6 (anchored with $s so it does not change if the formula is copied around).

The original answer is invalidated by OP stating they are using Google Sheets, not Excel. The revised answer based on that and the sharing of their sheet is here:

You need to replace the range references in the top formula (D2:D105 and E2:E106) with something like this snippet from the second formula:

INDIRECT("E2:E"&ROW()-1)

INDIRECT() lets you input a string and it will be converted to a range. The text E2:E gives you most of the range but it still needs that second row number. ROW() returns the row of a cell. If you don't tell it which cell to use, it uses the same cell the formula is in. ROW()-1, then, gives you the row above your cell. Having now seen your sheet, you just need to adjust the -1 to be whatever is appropriate based on where the formula is relative to the end of your data.

The best option depends on how your data is setup.

Reference the entire column:

=SUMIF(D:D,C108,E:E)

This will always capture everything which will automatically include any new data at the bottom. You should not place this formula in either of the impacted columns (ie, don't put it anywhere in column D or E).

You said in the comments that you tried this and got a #REF! error. This likely means that some cell in column D or E is a #REF! error. If you try to reference an error in a formula, that formula also returns an error. Look for a cell with an error in it and, if you find one / many, correct it / them.

Go from row 2 to the first blank row:

=LET(blankRow,ROW(INDEX(D:D,MATCH(TRUE,INDEX((D:D<>0),0),0))),SUMIF(INDIRECT("D2:D"&blankRow),C108,INDIRECT("E2:E"&blankRow)))

This got complicated but let's take out a big piece:

LET(blankRow,ROW(INDEX(D:D,MATCH(TRUE,INDEX((D:D<>0),0),0))),...

The LET() function lets you take some big formula and give it a short name. Our big formula is ROW(INDEX(...)) and it finds the first blank row in a given range. That's why I gave it the name blankRow. This formula looks in D:D but you could change it to look in E:E if you prefer. Now that we know what that is, let's look at the second piece:

...SUMIF(INDIRECT("D2:D"&blankRow),C108,INDIRECT("E2:E"&blankRow)))

That looks a little more familiar. It references columns D and E but only the rows from 2 to blankRow. The INDIRECT() function takes a string version of a range address and turns it into a range. As you add data to the bottom, blankRow from the first half will get bigger so the range references in the second half will grow accordingly.

Go from row 2 to the last non-blank row

=LET(nonBlankRow,SUMPRODUCT(MAX((D:D<>"")*ROW(D:D))),SUMIF(INDIRECT("D2:D"&nonBlankRow),C108,INDIRECT("E2:E"&nonBlankRow)))

Same basic idea. The first half defines the last non-blank row to be called nonBlankRow:

=LET(nonBlankRow,SUMPRODUCT(MAX((D:D<>"")*ROW(D:D))),...

The second half gets a sum based on the ranges from row 2 to that last non-blank row:

...SUMIF(INDIRECT("D2:D"&nonBlankRow),C108,INDIRECT("E2:E"&nonBlankRow)))

I'm not too clear if this is the result you want, but perhaps something similar:

=QUERY(Sheet1!A1:E19,"select A, D, C, E, B where A like 'makati%'",1)

See my added sheet, Makita-GK, in your sample.

Is that doing what you want? If not, please provide more details of what you want the result to be. Note that I added some data values in your Sheet1 data. If that isn't the type of data you have, please adjust it accordingly.

If necessary, please update your question to clarify the following.

Do you mean "where D='makati'...." or "where A='makati'...."? Column D is lastname in your sample data. Also, you had no data values with makati1 or makati2 in column A?