Cell References with Addition and Subtraction as pointers

[shoddy]

I know I did this before but for whatever reason I don't remmember how. Heres the scenario. I have 2 worksheets:

Worksheet1
Column A
Row1 Apple
Row2 Mary
Row3 Jane
Row4

Worksheet2
Column A
Row1 formula????

formula - I am looking to a formula that looks sort of like this. Give me the value of Row4 - 3 so that the result is apple. The reason that I am looking for this is because I have groups of 10 in worksheet 2 that relate to one row in worksheet 1 and I want a formula that I can copy down to each 10 so that it keeps the 10 to 1 ratio.

 

[Asator]

I'm guessing you're gonna be using offset(), address(), indirect(), etc, but I guess I'm not really following where you're going with this. Can you give more details?

 

[shoddy]

ok I will try to be clearer.

Worksheet 1

Column A
Row1 Apple
Row2 Mary
Row3 Jane
Row4 Jack

Worksheet 2

Column A Column B Formulas used in columnB
Row1 Company1 Apple =worksheet1,A1
Row2 Company2 Apple =worksheet1,A1
Row3 Company3 Apple =worksheet1,A1
Row4 Company4 Apple =worksheet,A1
Row5 Company1 =worksheet,A5
Row6 Company2 =worksheet,A5
Row7 Company3 =worksheet,A5
Row8 Company4 =worksheet,A5

See how when I copy the formulas in worksheet 2 in column B down to rows 5 through 8 in automatically references A5 in worksheet 1. I want the formula to reference A2. So thats why I am looking for a formula to insert in column b 5 thru 8 so that it automatically does worksheet A5 - 3 which would result in A2 and for the rest of the spreadsheet when I copied down I would not have to make any changes. FYI, company in my actual spreadheet is 35 companies and worksheet 1 has 150 rows. I know its probably an easier way but Im not that good with excel.

thanks

 

[SaladProblems]

ok I will try to be clearer.

Worksheet 1

Column A
Row1 Apple
Row2 Mary
Row3 Jane
Row4 Jack

Worksheet 2

Column A Column B Formulas used in columnB
Row1 Company1 Apple =worksheet1,A1
Row2 Company2 Apple =worksheet1,A1
Row3 Company3 Apple =worksheet1,A1
Row4 Company4 Apple =worksheet,A1
Row5 Company1 =worksheet,A5
Row6 Company2 =worksheet,A5
Row7 Company3 =worksheet,A5
Row8 Company4 =worksheet,A5

See how when I copy the formulas in worksheet 2 in column B down to rows 5 through 8 in automatically references A5 in worksheet 1. I want the formula to reference A2. So thats why I am looking for a formula to insert in column b 5 thru 8 so that it automatically does worksheet A5 - 3 which would result in A2 and for the rest of the spreadsheet when I copied down I would not have to make any changes. FYI, company in my actual spreadheet is 35 companies and worksheet 1 has 150 rows. I know its probably an easier way but Im not that good with excel.

thanks

Is there any pattern to when it moves to the next selection?

 

[Asator]

Nevermind... I think actually get where you're going with this. Give me a minute.

 

[SaladProblems]

There's a couple ways you could do this, my question is whether you just want to fill these items in one time, or if you need to have them auto-populate down the road.

If you just need to fill them in once, you could try:

=OFFSET(Sheet1!$A$1,ROWS($C$1:C1)/4,0) replace the 4 with 10, I was just using your example which had sets of four

But if you resort those lists the results could get ugly.

That being said, if these associations are permanent, you can just do a copy/past special>values over them and be done with it.

 

[Asator]

Here we go
Sheet1:
<style> <!-- BODY,DIV,TABLE,THEAD,TBODY,TFOOT,TR,TH,TD,P { font-family:"Arial"; font-size:x-small } --> </style> <table border="0" cellspacing="0" cols="1" frame="VOID" rules="NONE"> <colgroup><col width="86"></colgroup> <tbody> <tr> <td align="LEFT" height="17" width="86">1: Apple</td> </tr> <tr> <td align="LEFT" height="17">2: Mary</td> </tr> <tr> <td align="LEFT" height="17">3: Jane</td> </tr> <tr> <td align="LEFT" height="17">4: Jack</td> </tr> </tbody> </table>
With a named array "names" of A1:A4
<style> <!-- BODY,DIV,TABLE,THEAD,TBODY,TFOOT,TR,TH,TD,P { font-family:"Arial"; font-size:x-small } --> </style> <table border="0" cellspacing="0" cols="2" frame="VOID" rules="NONE"> <colgroup><col width="86"><col width="86"></colgroup> <tbody> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17" width="86">Co.</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" width="86">ID</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company1</td> <td style="border-top: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Apple</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company2</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Apple</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company3</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Apple</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company4</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Apple</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company1</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Mary</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company2</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Mary</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company3</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Mary</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company4</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Mary</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company1</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Jane</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company2</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Jane</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company3</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Jane</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company4</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Jane</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company1</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Jack</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company2</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Jack</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company3</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Jack</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company4</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" align="LEFT">Jack</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company1</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" sdval="0" sdnum="1033;" align="RIGHT">
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company2</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" sdval="0" sdnum="1033;" align="RIGHT">
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company3</td> <td style="border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" sdval="0" sdnum="1033;" align="RIGHT">
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Company4</td> <td style="border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" sdval="0" sdnum="1033;" align="RIGHT">
</td> </tr> </tbody> </table>
With a named array "array" of A2:B21
For the cells under ID:
{=OFFSET(Sheet1!$A$1,((ROW(Array)-2)-(MOD(ROW(Array)-2,ROWS(names))))/ROWS(names),0)}

This will let you use as many companies as you want, and as many names as you want.

 

[shoddy]

ok, I must be doing something wrong, but when try to replicate what you did and I copy your formula in B2 make it an array and copy it down I get "Apple" as the result in all cells in B2. what am I doing wrong.?

 

[SaladProblems]

On the one I did you won't have to make it an array. You can just drag it down or hit ctrl+enter instead of crtl+shift+enter.

If you're talking to the other guy, ignore this.

 

[shoddy]

Actually I was referring to the other formula but yours worked like a charm Salad. Many thanks. You saved me about 5000 lines worth of copying and pasting.