Varibles inside formulas!

**jonaslol** · 07-18-2007, 06:48 AM

Greetings,
THis is the oringinal formula:
=c:\Path\[test.xls]Test Resultst'!$H$19
So basically it's taking the information from the cell in test.xls, in sheet test results, H 19. This works perfectly. Now, I want the end number, 19, to be the value of a cell on the original worksheet, etc:
=c:\Path\[test.xls]Test Resultst'!$H$(W14)
So on the oringinal worksheet (that we want to copy this information from), the value in cell W14, let's say it's 19.
So it will go to H19 in that cell.

Basically I want to use a cell value inside the formula, but I cant get it to work!
Help would be greatly appriciated, I'm a newb, so please respect me a little bit

Cheers.

**EdMac** · 07-18-2007, 09:04 AM

Hi

You need to use INDIRECT

=indirect("c:\Path\[test.xls]Test Resultst'!$H$"&W14)

Ed

**jonaslol** · 07-18-2007, 09:27 AM

Thanks a lot for your reply ED,
I did as you told me, and pasted this formula:
=INDIRECT("E:\files\[test_e_1801.xls]Test Resultst'!$H$"&W14)

THe file is correct, since:
E:\\files\test_e_1801.xls
exists
The sheet name there is
Test Resultst
And in W14 there is 19 in the original file.
Stile im getting the;
#REF!
error?

Any ideas? Thank you so much!

Edit: W14 is underlined as a error by the way... :/

Updated:
I realised that this wouldn't help me to much, so I figured out this formula:
ROW()+5
So maybe if I put in that formula inside W14, would that work? Or could I somehow include getformula function somehow. Anyhow insted of getting 19 I like to get that formula, if possible

I know I'm asking for much, but I tried googlin for hours and trying solutions, and I just cant figure it out...

Thank you

**EdMac** · 07-18-2007, 11:17 AM

Hi,

The source file has to be open to use indirect - if it's not it will give an error.

let me know how you go

Ed

**jonaslol** · 07-19-2007, 02:04 AM

Ok, I understand but I still get a error. The thing is that I have the original workbook open, I have the workbook to get information from open, and I have the source (alt f11) openm, dispite I have nothing in there...
err :/

**jonaslol** · 07-19-2007, 02:07 AM

Ok, I understand but I still get a error. The thing is that I have the original workbook open, I have the workbook to get information from open, and I have the source (alt f11) openm, dispite I have nothing in there...
err :/

Sorry for double post, came up error so I tried twice, sorry.

**EdMac** · 07-19-2007, 05:03 AM

Hi

Have you tried using the formula evaluation tools to see where the problem arises (Tools >formula auditing >evaluate formula).

have you got a typo somewhere, is a name slightly different - if all is right, it should work.

Ed

**jonaslol** · 07-19-2007, 05:43 AM

Ok I'm soon giving up, :P
This is how the cell looks right now:
=INDIRECT("E:\file\file info\file Test Data\folder that it is in\[testdata.xls]test'!$H$"&W14)
Some folders contains spaces.

testdata is the name of the file.
test is the sheet name
Directory is correct.
All above been double checked many times now.

Both files are in the same folder!
The original folder that the data is supposed to come in is named:
test
So I got test opened, test data opened. I hit enter in the formula and the error occur.

I tried the formula evaluation thing.
First up W14 is underlined.
Then it is replaced by 19 and everything is underlined.
Then it changes a bit, and becomes like this:
=INDIRECT("E:\file\file info\file Test Data\folder that it is in\[testdata.xls]test'!$H$19")
It is Italic and underlined.
Then I get the #REF! error

Thank you for your time