A Complicated Matter...

[SamuelT]

Hi all,

So I don't know if this is possible, but here goes...

I have a spreadsheet that lists a number of projects (we'll call it
'parent'), and also other projects ('child') that it relies on. What
I'm trying to do is when both the 'parent' spreadsheet is entered in a
cell, and the 'child' in another, that Excel then checks in another
spreadsheet (the 'parent' project status report) and places the
description of the dependency and RAG status (i.e. Red, Amber, Green)
in two corresponding cells in the 'parent'.

I'm thinking perhaps there is a means of doing this using IF and
VLOOKUP, however I'm not sure if one can do VLOOKUP across two
spreadsheets.

Any suggestions would be greatly appreciated!

TIA,

SamuelT

 

[Bob Phillips]

Why don't you try it?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[SamuelT]

Useful. Funnily enough...I have been!

Main problem is that Excel doesn't seem to like looking at a table
array in the other spreadsheet. So currently my formula is looking
something like this:

=IF(B2="",VLOOKUP(B2,'[Parent.xls]area',4,FALSE),0)

'Area' being the table array.

The above formula returns the 'The formula you typed contains an error'
and cites the table array as the problem.

 

[Pete_UK]

What you are saying here is "if B2 is blank then try to find an exact
match of it in the lookup table" - presumably your formula should
start:

=IF(B2<>"", VLOOKUP(B2 ....

The named range "area" is not known in the sheet where this formula is
active, so you may need to change your reference to:

'[Parent.xls]Sheet1!'area

Hope this helps.

Pete

 

[SamuelT]

Thanks Pete. That's clarified that for me.

I always thought that "" indicated anything, rather than nothing. You
learn something new every day!