Formula using pattern

[Alex Braga]

Hey guys!

I need a formula that skips a given number of columns when referencing the cells I need. Let's consider the following formula:

Please Login or Register  to view this content.

This is the base formula I need. The next cell must be:

Please Login or Register  to view this content.

and the next one:

Please Login or Register  to view this content.

and so on...

But, obviously, if I click and drag the formula doesn't follow this pattern, and I need it to be applied to multiple cells in a row, otherwise I could do it manually instead. That's when I came across the INDIRECT function that I saw here: excel.tips.net/T003067_Copying_Formulas_using_a_Pattern.html
I understand that I should use the function COLUMN instead of ROW that he used in the example, but I have no idea how to write the formula combining the INDIRECT function with IF without getting an error

Does anyone have a clue about that?

[Pete_UK]

Do you have a row of headers in both sheets that you could use to determine which column to return data from? If so, it would be relatively easy using INDEX/MATCH, without having to use the volatile INDIRECT function.

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[Alex Braga]

Here's the sample .xlsx file attached

The initial formula is written on B16 from the first sheet. It should follow the row to C16, D16, (...), always moving 3 columns to the right on the first parameter of the IF function and 4 to the right on the second and so on, just like I showed on my last post

[Pete_UK]

You can use this formula in B16 of the first sheet:

=IFERROR(INDEX(Anotados!$15:$15,MATCH(B$2,Anotados!$3:$3,0)+1),"")

You may need to change the commas ( , ) in the formula to semicolons ( ; ), depending on your regional settings. Then you can copy it across as required. If you put some made-up data in the second sheet, so that you have some different numbers in the totals on row 15, then you will see the effect.

Hope this helps.

Pete