Locked Cells Change References

[jnsturze]

I have a main sheet that references other sheets in my document. I have a refreshable query that changes based on a number i put in.

what i would like it to do is from my main table, reference the new data. so for example

when i change the "facility" to 126, it reads like this

A1 = Dept1 (references =Sheet3!$C$6)
A2 = Dept2 (references =Sheet3!$C$7)
A3 = Dept3 (references =Sheet3!$C$8)
etc

however, when i select a different facility, and refresh the data, it changes the locked cells i had in my main sheet. so instead of
a3 =dept3 it references =Sheet3!$C$22) and skips the other 14 departments and goes right to the last one.

thanks for the help!

 

[ChrisR]

I have a main sheet that references other sheets in my document. I have a refreshable query that changes based on a number i put in.

what i would like it to do is from my main table, reference the new data. so for example

when i change the "facility" to 126, it reads like this

A1 = Dept1 (references =Sheet3!$C$6)
A2 = Dept2 (references =Sheet3!$C$7)
A3 = Dept3 (references =Sheet3!$C$8)
etc

however, when i select a different facility, and refresh the data, it changes the locked cells i had in my main sheet. so instead of
a3 =dept3 it references =Sheet3!$C$22) and skips the other 14 departments and goes right to the last one.

thanks for the help!

Can you post a screenshot? It's difficult to see what you need from the details you have given.........

Regards,
Chris

 

[Andrew Poulsom]

Try a formula like:

=INDEX(Sheet3!C:C,8)