I'm collecting vaccine information for the teachers in our school and because of privacy, teachers can only see their own staff's information. Therefore, I've created a worksheet for each teacher with their staff listed. A form went out to collect the data from staff members. I am trying to use the following formula to pull the data from the form responses into the teacher's spreadsheet, and it works in some spreadsheets but not others. I'm not getting the #REF to click on and connect the sheets. I think that's the issue. Has any seen this and/or know how to resolve? I feel like I'm missing something simple.
=IFERROR(VLOOKUP($B2:$B,IMPORTRANGE("1bQnPrmZwqQnziVsCktNE7hDNhMmzIbxqcZNDrXF716o","Form Responses 1!A1:J"),5,FALSE),"")
This works in one of the teacher's worksheets. The exact same formula does not work in the next teacher's worksheet. It doesn't throw any errors - just leaves blank data in the cell.
I'm in a time crunch here and any help is appreciated! Thank you!
before an IMPORTRANGE() will work nested in a formula it has to be "connected". The only way to prompt that #ref error and establish the "access" is to use the IMPORTRANGE() once first "Naked". That is, not nested in another function.
so just pick a new cell somewhere and try just a single IMPORTRANGE() of any particular cell. Then you'll be prompted to "allow access". Once you click allow, the other functions using IMPORTRANGE should work.