Linked Sheet Formula

[BeerBeer101]

I'll do my best to explain and provide a little picture (Sorry, unable to install XL2BB) to show:

I have a master sheet (shown) and it links to external sources. When I add or remove a rep, I need to reset the links. with 8 Reps and 9 linked sheets, it works out to HUNDREDS of links that need to be reset (luckily only once per year). The other issue is my Master sheet goes accross and my data sheets go down (it's the way they export - cannot change that).

Is there a way to automate the formula in C2, C3 onward to Change $3 to $4, $5, or even by a jump of 2, 3, 4 or even more cells (it's a pattern) so I would only update the link in B2 and C2 onward would "fall into line"?

I have provided the bare bones here - of course the sheets are in various locations on network drives - but the basics are the same.
appreciate the help as always!

Edit:
Correction. C2, D2, E2 etc... advancing the cell down by 1, or even a set number.

 

[James006]

Hi,
You could test, for cell B2

=INDIRECT("'[Rep 1.xlsx]Sheet1'!$B$"&COLUMN()+1)

 

[BeerBeer101]

Hi,
You could test, for cell B2

=INDIRECT("'[Rep 1.xlsx]Sheet1'!$B$"&COLUMN()+1)

that works briefly while the sheets are open, however the source in the formula did not update so I get an #href (looking for '[Rep 1.xlsx] without the full address).
I guess what I also don't see is any sort of starter reference - the starter reference I would be looking for is of course B2 in my master sheet rather then depending on the source sheet.

 

[James006]

Hi again,
Not sure to fully understand ...
But if your idea is to store the names in a hard-coded way, you can always Copy - Paste Values ...

 

[BeerBeer101]

Hi again,
Not sure to fully understand ...
But if your idea is to store the names in a hard-coded way, you can always Copy - Paste Values ...

Here's perhaps a better way to explain:

referencing the image above:
Cell B2 references a cell (b2) on an external sheet with formula ='[Rep 1.xlsx]Sheet1'!$B$3
if in cell C2 I wish to reference ='[Rep 1.xlsx]Sheet1'!$B$4, I have to manually link (cannot take the $ out and copy right - because the source sheet goes down.

so the formula needed in C2 would take the formula from B2, but change the 3 to a 4 (or a defined number - maybe I want to jump down 5 cells). then I could keep copying that over for the 12 months.

B2 formula: ='[Rep 1.xlsx]Sheet1'!$B$3
C2 formula: [reference B2 but change the last digit by one or defined number]
D2 formula: [reference C2 but change the last digit by one or defined number]

hopefully that is a better explanation?
thanks!

 

[James006]

Hi again,
In the formula, the portion COLUMN()+1 whenever the formula is located in Column B generates 3 ( = 2 +1 )
so, you could have an additional cell , say Z1, with whatever value you might want (0,1,3,5, etc..)
and adjust your formula with COLUMN()+1+$Z$1

Hope this will help

 

[Fluff]

How about

=INDEX('[Rep 1.xlsx]Sheet1'!$B$2:$B$1000,COLUMNS($B2:B2))

 

[BeerBeer101]

How about

=INDEX('[Rep 1.xlsx]Sheet1'!$B$2:$B$1000,COLUMNS($B2:B2))

That formula works when I am jumping down one cell below - but how would I opt to make it 2 or 3 or or more cells down as the jump? thoughts?

 

[Fluff]

This will return B2, B4 B6 etc

=INDEX('[Rep 1.xlsx]Sheet1'!$B$2:$B$1000,COLUMNS($B2:B2)*2-1)