Referencing rows issue

[EAGLEBUCKS]

Hi Everyone,
I had an issue earlier this week where if I added rows it caused my row references to be off in that worksheet. Thanks to your help I took care of this issue. However, I have an issue that is along the same lines.

Here it goes...

I have a separate workbook that pulls information from three different workbooks to total the number of hours worked. It basically is a final workbook that summarized or adds up all the hours spent on all jobs within our organization.

My issue is when I add a row into one of the three workbooks it causes my "summary" workbook to now be off and the graph is no longer looking at the right data. For example the equation I am using in the summary worksheet is ='J:\bd\BD-P Team Info\OP-PT2\Resource Planning\2011\Wk 23\[Wk 23_CA5257_Cost Planning.xlsm]Cost Plan'!L$656

If I add a line or delete a line the workbook Wk 23_CA5257_Cost Planning.xlsm it screws up the equation above.

Is there anyway I can make the "summary" workbook adjust to the cost planning row movement?

[NBVC]

So you always want the formula to look at cell L656.. even if you add or delete rows?

[EAGLEBUCKS]

No if I add a row to the cost planning sheet so now it is row L657 then I want the "summary" sheet to look at L657 as well

[NBVC]

So I guess one of the summary book is closed when the source is being changed and then when you open the summary you don't see the change?

If both books are opened, then the changes should reflect in the Summary book...

Otherwise, is there a way you can use Vlookup to get the value of interest or any other clue.. like it's the last number in the column always, etc...?

[EAGLEBUCKS]

I am not familiar with Vlookup but It does have "ME Transport Projected (hrs)" in column C of the same row. Could I use that?

[NBVC]

Probably, if it is the only ... or at least the first occurance, ... try:

=VLOOKUP("ME Transport Projected (hrs)",'J:\bd\BD-P Team Info\OP-PT2\Resource Planning\2011\Wk 23\[Wk 23_CA5257_Cost Planning.xlsm]Cost Plan'!$C:$L,10,FALSE)

[EAGLEBUCKS]

=VLOOKUP("ME Transport Projected (hrs)",'J:\bd\BD-P Team Info\OP-PT2\Resource Planning\2011\Wk 23\[Wk 23_CA5257_Cost Planning.xlsm]Cost Plan'!$C:$L,10,FALSE)

What is the 10,False part of it mean?

[NBVC]

Did it work?

VLOOKUP syntax: VLOOKUP(Lookup_value,table_array,column_index_number,[range_lookup])

Lookup_value: "ME Transport Projected (hrs)"
table_array: J:\bd\BD-P Team Info\OP-PT2\Resource Planning\2011\Wk 23\[Wk 23_CA5257_Cost Planning.xlsm]Cost Plan'!$C:$L
column_index_number: 10
range_lookup: FALSE

the 10 means return value from 10th column in the C:L table array

the FALSE tells Vlookup to look for an exact match as opposed to approximate match (TRUE).

Look at VLOOKUP help in Excel files for more details and samples.

[EAGLEBUCKS]

No I am having no luck with it still. Is there anything else I can use beside VLookup?

[NBVC]

How is it not working?

I think Vlookup is your best chance when looking at external workbooks.

[EAGLEBUCKS]

No I cannot get it to work. I have no clue why. All I can get it to say is either "FALSE" or "0" in the cell.

It should say 20 in that cell

[NBVC]

Did you alter the formula in any way.. not sure how you get FALSE....

If you get 0, you probably have found a match in column C, but there is a blank in corresponding cell in Column L of the same row... are you sure that column C is where we are looking for a match and column L is the column we want to pull values from?