using a different way of cell directions

[Hein]

Hi all
I know it can be done, but can't get it working

I am making a file for somebody else. Therefore the location and maybe the name can/will change.

To avoid having to check each and every formula I have made the following setup:

 column A        column B
Pathname         'C:\
Filename         [Source budget.xls]
Sheetname        Budget_P1100'!

In the "data cell" I have made the formula: =B1 & B2 & B3 & A7
So A7 is the cell in the other file.
The result is that the actual contents of cells B1, B2 and B3, together with the contents of cell A7 (from the same sheet) are "connected".

The results now are: c:\[Source budget.xls]Budget_P1100'!Kortingen

What can I do to get the desired results

Thanks in advance
Hein

[EdMac]

Check out the INDIRECT function

[sweep]

Hi,

I think you need this

=INDIRECT(B1 & B2 & B3 & "A7")

or have I misunderstood?

[Hein]

Hi Dave,
Thanks for your reply.

I think we are on the right track, however:
if I use your function I get #verw! (I'm not sure how to translate this to English, but it is something like "directions are wrong")

If I use the "function builder" in Excel on this formula I get
=INDIRECT(B1&B2&B3;A7)
This results in #Value!

Trying to combine bits of the 2 formulas and/or remove the ", ' and/or ! in B1,2 or 3 doesn't give the desired results.

So I think you have pointed me in the correct direction, only a final touch is needed.

Any ideas?

Thanks
Hein

[Hein]

Thanks all for your contributions!

I have it working!
Apparently it is necessary that is the source file is open, it also has to be in the same directory !?

1 question remains: is it possible to copy this formula to other cells, without having to change the last part of it manually?
The formula now is

=INDIRECT($B$1&$B$2&$B$3 & "b7")

Hein

[Cheeky Charlie]

Indirect is the tool, but:
* Is volatile (slows down workbook calculation - not too bad if it's only a few formulae)
* Only works when the referenced workbook is open - so:
indirect([Source budget.xls]Budget_P1100!<cell address>) (note the ' are missing) would work if the workbook was open.

Not terribly helpful I'm afraid...

CC

NB indirect only works on open workbooks - so you can't define the path and therefore you don't use quotes for the reference

[martindwilson]

how do you want it to increment ? is b7 to b8 b9 or b7to c7 d7
for the former use row() instead of cell row number
&"b"&ROW()
this of course uses the row the formula is in so if your completed formula starts in row 1 youd' have to ad 6 to it to ref b7
&"b"&ROW()+6

[Hein]

Hi Martin
Thanks for your answer!

I'm nearly there!
Just 1 item to go. I would like to increment to both rows and columns
So B3 gets B4 or B3 gets C4
This is important because my goal is to fill the top left cell with this formula (with VBA). Then copy it to the rest of the area.
After this I want to copy this area and paste it to values.
After this the file can be distributed to various people.

I have changed your part (&"b"&ROW() ) to
&Column()&Row()
This means I am in fact back to the 1st attempt (B1 & B2 & B3 & A7)

The error message is therefore the same
Is there a way to tackle this problem?

Hein

PS
I've tried brackets etc but they don't do the trick


PPS
I think i understand the problem
Column() returns (e.g.) 4, instead of D
So the result is 45 and not D5

PPPS
Got it!!
I have changed the last part with ADRESS(Row();Coulmn())
This does the trick

Thanks for putting me on the right track!!