Automatically updating worksheet references in formulas based on cell row position relative to other cells

[BKGLTS]

Hello,

I am trying to automatically update references in formulas based on the row position of its cell.

For example (Sheet1):
A1= 1 B1= ='WBS1'!$C$13
A2= BLANK B2= BLANK
A3= 2 B3= ='WBS2'!$C$13
A4= BLANK B4= BLANK
A5= 3 B5= ='WBS3'!$C$13

In this example it shows the results of how I would want it to work.
Say I selected the entire Row 3 and copy/pasted it to Row 5.
The only thing that needs to be updated in the B5 formula would be the sheet reference, changing from WBS2 to WBS3.
The formula in the A column will automatically update to 3 in this example.
HOWEVER, the A column values could be any type of alpha-numeric value so cannot be used to determine the position required below.
So, B5 formula would look at column A and see that there are only 3 populated cells in the A1:A5 range and that it was in the 3rd position from the top.
Therefore it would append the WBS2 in the formula to WBS3.
The number after the WBS indicates the position in column A, which was found.

The way this works is, when I copy and paste a new row it automatically references the correct worksheet for its position.
1st column A value will always reference WBS1 worksheet

2nd column A value​

will always reference WBS2 worksheet

3rd column A value​

will always reference WBS3 worksheet​

and so on...

Thank you!
B

​

 

[Roderick_E]

I don't quite understand but look into INDIRECT & ROW() & COLUMN() -- Then you may need some IF statements to determine what row/column the pasted formula is on. I think that will help.

 

[BKGLTS]

Let me try and simplify the explanation: