Assign a text value to a calculated address


i tried to explain my problem in the excel sheet attached
I would really appreciate any help.

keep safe

The Question:
I want to automatically write text "EML" to the calculated addresses.

in my example attached ,
I want to automatically write text "EML" to this cell ( AC9) because AC9 is the calculated address. 

the calculation is:

  • 1.  I will find the first  "PROD" text  on the same ROW that i want to insert 'EML" ;  which is on cell W9        
  • 2. Then I will find that 'PROD's cell address which is  W9 ( column W =23 ,          row = 9) 
  • "Then I will add the  " Transportation Weeks" value , which is on cell J9 = 6     ( column 10 , row 9 ),  and which is inserted by the excel user ,  to  the cell adress I foundabove" 
  •  So , our solution  should automatically write EML to the calcultated adress/cell   which is AC9  [column29=( 23 + 6 ) and Row=9]                                                                                                                                                
    PS : If the PROD cell adress changes or the "" Transportation Weeks"" value changes  ,  the calculated EML address has to change accordingly                                     
    I have come to some extend , please see my helper calculations on  E9 F9 G9 H9 



No formula can write anything to any cell other than the one in which it resides. If you truly want to write something to a cell you will need VBA. A VBA-based solution might act on any change of cell J9. It's not difficult to do and not very complicated in itself but your workbook is big and adding VBA does add a level of complication which will work to make it less unmanagable. Therefore I suggest a solution based on worksheet functions.

Enter the function below in cell X10. From there copy it up, down and to the right as far as you need. 

=IF(COLUMN()=COLUMN($W10) + $J10,"EML","")

I hope this solution will satisfy your requirements.



Hey thanks 4 ur ineterst
i think VBA will be the solution because the Prod cell will change, it cant be static,  therefore your function wont work ,
barqin Apr 8, '20 at 4:32 am
It's impossible to write VBA unless you describe how the Prod cell (W10) can be identified if its location isn't fixed. Then if a worksheet function can identify it that same function I already provided will be the best solution for you, with a slight alteration in regard to its COLUMN($W10) component. One way or the other, you can't make headway without defining a generic way of identifying the Prod cell.
Variatus (rep: 4218) Apr 8, '20 at 5:36 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login