How to apply an IF condition when referencing another workbook and sheet

[defender]

Hi all,

i would like to know how to apply an IF condition when referencing another workbook and sheet.

so far i have:

=('[running2020.xlsx]method'!C2:50)

the cells in column C from the spreadsheet 'running' and sheet 'method' start AB.00001.20, CD.00001.20, EF.00001.20, GH.00001.20. I would like all references starting EF and GH to be extracted only.

thanks for any help on this

 

[jasonb75]

i would like to know how to apply an IF condition when referencing another workbook and sheet

The same way that you would if it was the same sheet, just with the workbook name and sheet name included in the formula.

I think this should do what you need

=IFERROR(INDEX('[running2020.xlsx]method'!$C$2:$C$50,AGGREGATE(15,6,ROW(method'!$C$2:$C$50)/((LEFT(method'!$C$2:$C$50,2)="EF")+(LEFT(method'!$C$2:$C$50,2)="GH")),ROWS($C$2:$C2))),"")

 

[defender]

The same way that you would if it was the same sheet, just with the workbook name and sheet name included in the formula.

I think this should do what you need

=IFERROR(INDEX('[running2020.xlsx]method'!$C$2:$C$50,AGGREGATE(15,6,ROW(method'!$C$2:$C$50)/((LEFT(method'!$C$2:$C$50,2)="EF")+(LEFT(method'!$C$2:$C$50,2)="GH")),ROWS($C$2:$C2))),"")

Thank you. I should have stated that 'method' is not the sheet name, but tab name on the spreadsheet 'running.

grateful if you could assist further

 

[jasonb75]

Tab name and Sheet name are the same thing, the formula probably didn't work because I forgot to include the workbook (spreadsheet) name when I was using copy and paste to create the formula.

=IFERROR(INDEX('[running2020.xlsx]method'!$C$2:$C$50,AGGREGATE(15,6,ROW('[running2020.xlsx]method'!$C$2:$C$50)/((LEFT('[running2020.xlsx]method'!$C$2:$C$50,2)="EF")+(LEFT('[running2020.xlsx]method'!$C$2:$C$50,2)="GH")),ROWS($C$2:$C2))),"")

 

[defender]

Tab name and Sheet name are the same thing, the formula probably didn't work because I forgot to include the workbook (spreadsheet) name when I was using copy and paste to create the formula.

=IFERROR(INDEX('[running2020.xlsx]method'!$C$2:$C$50,AGGREGATE(15,6,ROW('[running2020.xlsx]method'!$C$2:$C$50)/((LEFT('[running2020.xlsx]method'!$C$2:$C$50,2)="EF")+(LEFT('[running2020.xlsx]method'!$C$2:$C$50,2)="GH")),ROWS($C$2:$C2))),"")

great thank.

If i want to use =('[running2020.xlsx]method'!C2:50) how can i return blank if the condition is not met?

 

[jasonb75]

The formula does that already, that is the purpose of IFERROR.

 

[defender]

The formula does that already, that is the purpose of IFERROR.

Just looking to use this now =('[running2020.xlsx]method'!C2:50) without the other condition of specific references, and if this condition is not met return blank.

 

[jasonb75]

There is no condition there to return blank, the reference is not even valid.

 

[defender]

There is no condition there to return blank, the reference is not even valid.

the data being extracted is dates, if the there is not date in the cell that i am extracting from, when i drag the formula down it returns 00/01/1900 instead of blank

 

[jasonb75]

Now if you said that earlier I could have given you the answer earlier, I can write formulas but not read minds.

=IF('[running2020.xlsx]method'!C2="","",'[running2020.xlsx]method'!C2)