Hi r/excel!

​

I am trying to reference a cell in a different worksheet, to essentially create a duplicate of that sheet. This is so I can manipulate a few key cells before I pass it all to Power BI and create some reports from it.

I understand that simply using ='Sheet'!A1 won't work very well, as when people using the main sheet cut and paste values around, my references will move also.

I thought to tackle the problem using =INDIRECT("'Sheet'!A1"), which I guess works. But when I drag the formula across the other cells, it takes the value as literal, and copies that exact formula.

My next thought was to use something like =INDIRECT("'Sheet'!"&COLUMN()&ROW()). This doesn't work though, it gives me a #REF! error.

What am I doing wrong here crew? What is the best way to make this work?

​

Thanks