Here is a way to dynamically change a formula to reference data in another worksheet when it is copied across columns:
You can download the file here to follow along. If you get a preview, look for the download arrow in the upper right hand corner.
This is one blog post that is better understood from the video. Some key items that you might take away from this are:
The ultimate formula used is: =INDIRECT($A4&”!”&ADDRESS(ROW(A1),COLUMN(A4),4))
The descriptions of the functions used are:
The ADDRESS function uses 3 components: Row, Column, and Absolute Reference. The codes for the Absolute Reference are:
Enjoy, and Happy Excelling!