Originally Posted by

**Pauleyb**
Okay, I was wondering where the Access and patronizing comments came from. I thought you were reading too much between the lines or people had sent you emails.

About Access. It probably is the right tool. And, since many people buy the Office suite, you may have it. But, you have chosen to use Excel, and I have seen people use Excel as a word processor, database, project planner, etc. It is not efficient, but it is flexible enough to do some work.

I'll be blunt. What you are asking for is a lot of tedious work. My attempt to help simplify the equation was met with the statement that sheet2 is essentially unmodifiable. Could someone come in and give you the equation you look for? Probably, but **I** would consider it drudgery and not something that is challenging in a fun way. Other people may have different views, but the fact that your question has remained unanswered is a good indicator that people are viewing this as a problem to stay away from.

Honestly, it would probably take me about an hour to come up with the perfect formula. Not the time I have. But, I do want to help you solve your problem.

Look at the example I provided. Understand it. It provides the foundational equations you will need to use: Offset, Mod, Int, and Row. You will also likely need If and Column.

Now, look at the table you want to create. In each of those cells put in the x,y coordinate needed to get that value assuming the origin is A1 on your second sheet. For example, your Sheet1!B1 would be (0,1) since you want 0 rows and 1 column offset from Sheet2!A1. Sheet1!B2 would be (0,4) since you want 0 rows and 4 columns from Sheet2!A2. Last example, Sheet1!B3 would be (1,4) since you want 1 row down and 4 rows over from Sheet2!A1 to get to 'Data 1 Aa'.

Once that is completed, look at those numbers. Those are the numbers you need to calculate for the OFFSET function. Using other functions, you need to figure out how to calculate those numbers. For example, 'Header 4' repeats every 6 rows starting on row 2. So, that means you want (0,4) every time you are on rows 2, 8, 14, etc. Which is 2+6*n. To check for this in your spreadsheet, you would do something like:

=IF(MOD(ROW()-1,6)-1=0, OFFSET(Sheet2!A1,0,4), xxxxxxx), where xxxxxxx will be your other equations. Of course, this gets even more complex because you will also need to consider the column you are in, so that you also need to utilize the COLUMN function and bring that into your offset. And that is exactly why your request is so arduous.

Am I being patronizing? Your call. I view it as trying to teach you how to fish so that you can solve this problem and any future ones on your own.

Pauley

## Bookmarks