-1

How can I insert a formula into cell reference to another sheet

For example,

General Formula

Cell D[x] = Analysis!I[x*9 + 5]

e.g.

Cell D1 =Analysis!I16 (Analysis Sheet Cell I 14)

Cell D2 =Analysis!I25 (Analysis Sheet Cell I 23)

Cell D3 =Analysis!I34 (Analysis Sheet Cell I 32)

How do I type it as a formula ?

See image

WXYZ
  • 53
  • 2
  • 9

2 Answers2

0

@WXYZ try to use: =INDIRECT("Analysis!" & ADDRESS(ROW()*9+5,9))

Error 1004
  • 7,877
  • 3
  • 23
  • 46
  • Hi Error 1004! Really appreciate your help but I needed to do more than that! As you can see I am only taking every 9th row from the Analysis sheet (Cell D[x] = Analysis!I[x*9 + 5] ), so I would like to create a formula so I can just copy and paste the formula until D[100] without having to alternate between sheets and clicking every single time – WXYZ Oct 20 '18 at 07:20
  • For now when I copy D1 formula to D2, I will get the value from I15 instead which is wrong because I actually need the value from I23 (every 9th row) – WXYZ Oct 20 '18 at 07:21
  • @WXYZ i edit my answer.i think the new answer fit your requirements! – Error 1004 Oct 21 '18 at 12:48
  • Hi Error 1004! Where do you specify the column alphabet (letter I) in the formula though? – WXYZ Oct 22 '18 at 02:53
  • @WXYZ i dont specify the column with a letter instead i specify the column with number.(the last 9 before the 2 brackets) – Error 1004 Oct 22 '18 at 04:53
0

If I understood what you are trying to do.. You want to have the value of each cells in column 'I' in the cells of the column 'D'. So you can simply do it graphically by typing '=' in cell D6 (for example) by fetching the value from the same worksheet or from another spreadsheet; by clicking again in the cell that you want to get its value. For deploying (to Extend) and applying the formula to other cells you can take a look at : https://www.extendoffice.com/documents/excel/867-excel-apply-formula-to-entire-column-row.html

It's very easy.

Mohamad TAGHLOBI
  • 581
  • 5
  • 11
  • Hi Mohamad! Really appreciate your help but I needed to do more than that! As you can see I am only taking every 9th row from the Analysis sheet (Cell D[x] = Analysis!I[x*9 + 5] ), so I would like to create a formula so I can just copy and paste the formula until D[100] without having to alternate between sheets and clicking every single time – WXYZ Oct 20 '18 at 07:19
  • For now when I copy D1 formula to D2, I will get the value from I15 instead which is wrong because I actually need the value from I23 (every 9th row) – WXYZ Oct 20 '18 at 07:21