0

I have a cell referanced to a remote server with formula:

=serv~10~0'!'23'*1

10 should be the variable referanced to a cell in the same sheet

I tried this but didn't work

=INDIRECT("serv~" & $A$1 & "~0'!'23'*1")

any idea how to do it!!

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
Adnan Al-Husain
  • 110
  • 1
  • 3
  • 17
  • What are you trying to reference?? File or whatever? via network or.....? – Peter L. Feb 15 '13 at 17:01
  • it's an installed program server that takes data from source server via network connection... so the cell will referance a data from a data server installed on my machine. if I change 10 to 20 this will referance to another field on the installed data server, how can I referance this part of the formula as a variable. – Adnan Al-Husain Feb 15 '13 at 21:48
  • I don't think such syntax is ever possible: using `INDIRECT` you may reference to Excel workbook only. Moreover, if you want to reference a cell into another workbook - this workbook MUST be opened, otherwise it will return `#REF!`. – Peter L. Feb 16 '13 at 07:06
  • so end of road with vba..thanks brother. – Adnan Al-Husain Feb 16 '13 at 19:43
  • no problem, glad you found the solution! You may submit it as your own answer and accept in a few days. – Peter L. Feb 16 '13 at 19:44
  • I mean at the end we have to go with vba to achieve this. list box click event handler. it's a little bit slow even with putting screenupdate off. – Adnan Al-Husain Feb 17 '13 at 04:38

1 Answers1

0

1 vba solution:

Private Sub ListBox1_Click()
Application.ScreenUpdating = False

Dim cmpCd As String
cmpCd = CStr(Cells(1, 1).Value)

'field title = company name
Cells(2, 3).FormulaR1C1 = "=serv~" & cmpCd & "~0'!'23'*1"

Application.ScreenUpdating = True
End Sub
Adnan Al-Husain
  • 110
  • 1
  • 3
  • 17