Thanks all i have figured it out now. Even though my data was in, as an example, Q10 the offset formula would only work if i set the fixed starting point 3 cells before - so Q7. Maybe that is because my data starts in sheet2 in column D.

Thanks for your time. As an aside i did try all the formula before making my comments. It was more a case of my knowledge level making it harder for me to understand the solutions.

That is a common issue/mistake many people make. They often "oversimplify" their problem, and provide an example that doesn't quite match their data structure.

Then, when they try to apply it to their actual worksheet, it doesn't work, because they do not know how to adjust the formula accordingly.

I usually advise people not to oversimplify the question or data structure for our sake. If you truly want a "plug-and-play" solution, make the details of your examples pretty much match exactly what you are working with.

With my formula:

Excel Formula:

`=OFFSET(Sheet1!$Q$1,COLUMN()-1,0)`

there are really only two things you need to mess with:

1. The starting point (

**$Q$1 **in this example). Always make this match the first entry in your list.

2. The "adjustment" to the offset, which is the "

**-1**" in the "

**COLUMN()-1**" part of the formula. This will always be a + or - some number. Once you find the number, it will be the same for every formula, so you don't need to adjust it after finding the correct initial value, just drag across.

If you are having trouble understanding how that second part works, it is easiest to see with an example.

If you tell me what the address of the first cell you are trying to pull is (is it Q1, is it Q7, is it Q10?) and let me know what cell address this first formula is going in (is it A1? is it B1?), I can walk you through how to figure out that value for part 2.