Link/reference problem

[Hieronymus5]

Dear Reader,

First of all, I'm new to this forum and haven't written/posted a lot (if any) on forums so please excuse me if I'm violating some of the internet's unwritten codes of conduct.

That being said, Im having a nasty problem with my references/links in Excel 2010. Of course, I'm hoping that you can help me to solve it!

Lets say I have two sheets open in one workbook (Sheet1 and Sheet2).

In Sheet 1, I have a bunch companies posted vertically in column A, while having the indicator 'Revenues' in the next column, column B. This looks like the following

<Column A> <Column B>
A1 Company 1 B1 10
A2 Company 2 B2 20
A3 Company 3 B3 15
...
AX Company X Y

In Cell A1 on Sheet2, I return the Revenues of Company 1 by simply linking to the value in Sheet1:

Cell A1: =Sheet1!B1

This yields the value 10. I do this for all values:

Cell A2 =Sheet1!B2
Cell A3 =Sheet1!B3
...

So far, all goes well. However (this is where it gets difficult, at least, for me), lets say that the companies are not listed directly below each other. Lets say each company is listed exactly 50 rows below the next one. This changes the situation to:

<Column A> <Column B>
A1 Company 1 B1 10

A51 Company 2 B51 20

A101 Company 3 B101 15

...

AZ Company Z BZ Y

Z = (1+50(n-1)) (with n companies) <- This last bit is irrelevant for my question but I figured I'd be 100% transparant on my first post (hihi!)

Now, my question is: is there a way, so that I can change the formula to match the new reference locations (i.e. change =Sheet1!B2 to =Sheet1!B51, =Sheet1!B3 to =Sheet1!B101 and so on) WITHOUT having to retype the changes inside each formula?

Of course, I couldve used OFFSET() to begin with and then add the extra rows. But lets say someone else made the workbook and used the little kiddy references described in the example (btw it was me. I made the little kiddy references).

Lets put this problem in other words:

On sheet1 there are again different companies listed in Column A and revenues in Column B. The companies are spread equally with row difference 50. Which yields the following list:

<Column A> <Column B>
A1 Company 1 B1 10
...
A51 Company 2 B51 20
...
A101 Company 3 B101 15

On sheet2 I want to reference, again:

A1 =Sheet1!B1
A2 =Sheet1!B51
A3 =Sheet1!B101

Notice that I need B to jump 50 rows each time I add a new reference.

Essentially I need something that basically allows me to change the part of the reference (B...) with +50 each step down. I've tried doing crazy stuff like:

Column A Column B Column C
A1 =Sheet1!B1 B1 C1 =50
A2 =A1+$C$1 B2 C2
A3 =A2+$C$1

But this obviously did not work (duh!) and did not cause the reference to rewrite itself to:

A1 =Sheet1!B1
A2 =Sheet1!(B1+50)

Oh and btw I've tried:

Column A Column B Column C
A1 =Sheet1!B1 C1 =50
A2 =Sheet1!(B1+$C$1)

Too!

Thanks,

Hieronymus5

EDIT:

Apparently I cannot attach files or copy paste pictures.

[oeldere]

@Hieronymus

according to the forumrules, it is usefull to add an title, which is usefull when you want to search on a topic.

Maybe you can change the title in a more appropriate one.

Second => you get better help on your question if you add an small excel file, without confidential information.

Please also add the desired result in your sheet.

Maybe there is a way with pivot table, to solve your problem.

[Hieronymus5]

@Oeldere

Thank you for your feedback. I have changed the title accordingly!

I figured my explanation rendered the need for an Excel file useless but, since you ask, I will provide one.

If possible I would prefer a solution where I can automatically adjust the formula in the cell instead of using pivots but if it works, it works! Im very curious to what you are proposing!

Hieronymus

[oeldere]

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Hieronymus5]

Thanks for the pointers! Here you go:

[oeldere]

With some addition to the sheet.

After that a pivot table.

See the attached file.

[Hieronymus5]

Thanks Oeldere,

Sorry for the late Reply!