Non-Consecutive Cell Referencing

[sinkerhawk]

Hi, folks.

I'm attempting to create a spreadsheet that contains links to non-consecutive cells in another worksheet. Let me elaborate. If I put "=A1" in cell B1 and then copy that down, it will update the relative formula consecutively, i.e. B2=A2, B3=A3, B4=A4, B5=A5, etc.". Yeah, that's elementary.

What I need to do is find a way to do the same thing, but to increase the resulting link.....so that if I copied the formula down column B I would get: B1=A1, B2=A5, B3=A9, B4=A13 (in which the row value of "A" gets 4 added to it for each single row change of B.)

So we all know that a formula like =A1+4 is going to add the value of A1 to 4 instead of increasing the row value of A1 to A5. Is there a way to do this? Otherwise, I'll have to manually change every cell reference for all my formulas beyond row 1.....tedious? You bet.

Just so you know, I have looked into vlookup, indexing, and matching and they will not do what I need. The values I need to reference are on different rows than the only value I can use for the lookup. So if the value in cell A1 satisfies the data I need, it will return cell K11....something like that. Also, there are several other data items I have to reference so I can't add additional if statements to give each line a unique value. I have too many data items per section and there is the limit of seven nested "if's"

I have yet to find anything in Excel that will do something like this which I think should be fairly simple (or at least should have been added as a standard option). Any ideas?

[BigBas]

Enter this into b1 and fill down:

=INDIRECT("A"&(ROW()-1)*4+1)

Let me know if that works.

[sinkerhawk]

This looks fantastic.....you just saved me a ton of work....Thanks!!!!

[BigBas]

I'm glad I could help.

[gmondun00]

I'm having the opposite problem.

I'm creating an excel model where I want to reference consecutive cells from a different sheet, but the links are not in consecutive order on the model. For example, I'll link C1 from the other worksheet
to the model, but when I copy this link it skips ahead to C5, etc. The rows in the model itself are 5 rows apart which is why I figure it is doing this.

Is there a solution to this?

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread