0

I am trying to automate my excel table as much as possible. Currently is working partially but trying to make it completely automated. The formula I am using is, ="SERVER_"&TEXT((ROW()-2),"00")&"::"&'Sheet 1'!D6 as you see the final output will be a combination of 4 sections 1st and 3rd are static text SERVER_ & :: 4th section 'Sheet 1'!D6is a reference cell value from another sheet 2nd section TEXT((ROW()-2),"00") is where i am seeking ideas. This example is used in cell F3. So as a result it will 01 and which is correct. But if I add a row above this line, I need to go back to this cell (now it will be F4) and change the formula to TEXT((ROW()-3),"00") to get the desired output "01". After that, I need to drag the corner to get 02, 03, 04 and so on to correct the value in the subsequent cell in F column. I am looking to replace with formula with a better one that will still give the value "01", will not change if a row is added or deleted and if I drag the corner it will update the value to 02, 03, 04 and so on.

Thanks in advance!

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
biplab54
  • 1
  • 1
  • Should the 4th secion of the draged down formulas all refer to `Sheet 1'!D6`, or should they increment? – chris neilsen May 08 '22 at 05:01
  • Are you using Excel 365? If so, you can create a Spill formula that doesn't require dragging down – chris neilsen May 08 '22 at 05:02
  • 1
    Something like `="SERVER_"&TEXT((ROW()-ROW($F$3)+1),"00")&"::"&Sheet1!D6 `. The only constraint is that if row 3 is deleted the formula will break. Any other row insert or delete, and it will still work – chris neilsen May 08 '22 at 05:05
  • @chrisneilsen - your formula is working and it is a great idea about using spill...was in my mind but hasn't been implemented. WIll try it next. – biplab54 May 08 '22 at 17:04
  • If you want it more automated the better option is power query take a look at this [example](https://stackoverflow.com/questions/72210897/excel-how-to-split-cells-by-comma-delimiter-into-new-cells/72211471#72211471) – Shane S May 12 '22 at 15:12

1 Answers1

-1

In your question you state that, the formula in question is located in cell F3.

And I believe you're trying always get the value of the cell that is at F1. That's why moving the formula down one row forced you to change from

ROW()-2

to

ROW()-3

However, if you always want the value at F1, you can simply use an absolute reference and avoid the calculation altogether:

="SERVER_"&TEXT($F$1,"00")&"::"&Sheet1!D6

If you're concerned that inserting a column might shift the formula, then you could just make the row absolute and leave the column relative:

="SERVER_"&TEXT(F$1,"00")&"::"&Sheet1!D6

WaltSource
  • 26
  • 1
  • 4