Yes, by using the INDIRECT function:

A1: Sales Report - WB 10.06

Then you can use:

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

I've been struggling with the same problem. Instead of writing a custom function, I add a different query string to spreadsheet_url in IMPORTRANGE in the hope that each time the page is refreshed, Google thinks it needs to fetch data from a new spreadsheet. I simply append a new timestamp to make the url unique each time. It's a blatant hack, but it has been working for me across a lot of sheets.

My formula previously looked something like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174","'Sheet1'!A1:B25")

And now it looks like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174"&"?"&now(),"'Sheet1'!A1:B25")

Update:

This method no longer works since Google no longer permits now() inside importrange(). See comment from Hugh below.

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)

script:

function SHEET(input) {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() ;
  if( (input>0) && (input <= sheets.length)) return sheets[(input-1)].getName() ;
  else return "invalid sheet #" ;
}
catch( err ) {
  return "#ERROR!" 
}
}

formula for first sheet:

=SHEET(1)

formula for 5th sheet:

=SHEET(5)

etc.

You can import data from another spreadsheet using the importrange() function:

=importrange(spreadsheet-key, range)

spreadsheet-key is part of the url of the spreadsheet that contains the data you want to import. It looks like "0AsaQpHJE_LShdFhSRWZBWWMxem1pZGxhWG1XZzlic0E". range defines the cells you want to import.

In your example, you would look for the spreadsheet-key in the URL of spreadsheet B, and then in spreadsheet A, cell A1, you would put:

=importrange("key-of-spreadsheet-b", "E:E")

Don't forget to surround the key and range with quotation marks.

if you dont like named ranges try:

={"Is Special"; ARRAYFORMULA(IF(A2:A<>"", 
 REGEXMATCH(FILTER({A2:B, D2:E}, {A1:B1, D1:E1}="First Name"), "b|d|e"), ))}

or like:

={"Count of one Properties"; ARRAYFORMULA(IF(A2:A<>"", 
 COUNTIFS(properties!A2:A, A2:A, 
 VLOOKUP(ROW(properties!A2:A),{ROW(properties!A2:A), properties!A2:Z}, 
 MATCH("type", properties!1:1, 0)+1, 0), "one"), ))}