I have 2 sheets in excel;

1. Sheet1: Data sheet where all the data is imported (we will call this 'Master')
2. Sheet2: sheet where I compare specific columns ('Comparison')

I need to copy in columns E and H from my Master to Comparison, starting at E3 and H3. To do this I use =Sheet1!E3 for each value (=sheet1!E4, =sheet1!E5 etc., etc.) going down. However, whenever I import data into Master, I insert another column and start new in E, moving all the data to the right. Now the issue is, the =Sheet1!E3 formula doesn't stay as =Sheet1!E3 but rather follows that data. I import 3 columns per data set, once it's copied in, the =Sheet1!E3 will turn into =Sheet1!H3. I need this formula to stay as E3. I have tried $E3 but the same thing happens.

What other formulas can I use to copy in data from another sheet but stay restricted to the first column of data? I have also thought about writing a macro (or at least trying as I know nothin about VBA code). If this is easer, I am open to suggestions.

CodePudding user response：

Use INDIRECT function:

=INDIRECT("Sheet1!E" & ROW())

This won't change when you insert new columns before column E.