Formula Values not auto-updating

[fsutaylor31]

Here is the formula from Workbook Test2.xls:

=IF('C:\Test\Project1\[Test1.xls]Tab1'!$T3="x",'C:\Test\Project1\[Test1.xls]Tab1'!A3," ")

This translates to: If Workbook "Test1", Worksheet "Tab1", Cell T3 = "x", then paste A3, Else paste a blank value.

Question 1: When I change the name of Test1.xls to something else, the formula in this workbook doesn't change. Is there any way to make it change with the new name?

Question 2: When I say paste a blank value as " " at the end, is there any way to make it just skip that item instead and paste NOTHING?

For example, the results when a line without 'x' is encountered in between two lines where it is encountered looks like this:

bt1

bt2

I'd rather they look like this:

bt1
bt2

Any help would be appreciated!!!

Brian

[rylo]

Brian

1) If you have test1 and test2 open at the same time, and do a save as with test1, then the formula in test2 will reflect the new name.

2) Are all the cells you want to test in column T of test1? Or is there a fixed range (say a defined name)?? Bringing them back without the blank lines is possible, but I'd like to get some idea of the structure and the likely range to test.

rylo

[fsutaylor31]

Yes, basically there is an entire row of data where column T is either an "x" or nothing. If it's an 'x', we want to copy a value over for reporting purposes (in this case, it's A3).

Then we propogate the formula into the next cell and copy over B3, E3, H3, and anything else we need.

Here is an example:

Column
A B C D E M T
Hello World 555 Tues May 13 x
Hello World 555 Tues May 23
Hello World 555 Tues May 30 x

Resulting spreadsheet currently copies over but has a space as such:

Hello World 555 Tues May 13

Hello World 555 Tues May 30

Thanks for the tip on Q #1 - I'll try that out.

Brian

[rylo]

Brian

Have a look at the attached. It will bring back the entries to sheet2.

rylo