Labels in formula

[a m spock]

I have a two labelled columns and 'labels in formula' activated. In the third
column i have a formula involving the labels. It works like a charm in the
first row.

However when I copy the formula down to second or third additional rows of
the third column, it recognises only the first row as the labelled value.

Can some one please help?

 

[a m spock]

sorry my worksheet got all messed up. i will repost the problem a little
later. my apologies.

 

[a m spock]

Here is the data and the formulas

BDate BRate BAmt SDate SAmt BRt BDt
01-01-2008 5 25 5 01-01-2008
01-02-2008 14 5 01-01-2008
01-03-2008 16 5 01-01-2008
01-04-2008 4 16 4 01-01-2008
01-07-2008 20 4 01-01-2008

The last two columns are formulas:
BRt= IF(BAmt>0,BRate,F25)
BDt= IF(BAmt>0,BDate,G26)

I seem to get the BRt figure right but not the BDt figure. I hope you can
figure out the data because it is not getting aligned.

Just in case, first and fourth row, the fourth and fifth cells are blank.
the other rows, first three cells are blank.

 

[Shane Devenshire]

Hi,

Microsoft has taken the Lables in Formulas option out of 2007 and for good
reason, there are too many problems with it. Use range names instead. In
your case select all the data and the titles at the tops of the columns and
choose Insert, Name, Create. Check only the Top row option and click OK.

Now you should be able to use those name in formula without problems. I put
the table in the range A1:G6. Both formulas worked just fine. But I don't
know what the references to G26 and F25 are all about, but with the data you
supplied it didn't matter.

 

[a m spock]

many thanks.

the reference g25 is to previous row same column, i.e. if corresp BAmt is
<=0, the BRt or BDt is carried forward.

This table is constantly expanding with new BUY or SELL transactions. How do
I make the Range Name dynamic so that data entered below the last row is
automatically added to the named range. This was my reason for using 'labels'.

 

[T. Valko]

How do I make the Range Name dynamic so

that data entered below the last row is automatically
added to the named range.

Use a dynamic range. There is one method to do this explained here:

http://contextures.com/xlNames01.html#Dynamic

 

[a m spock]

many thanks. that works. can i make the names available in other wsheets
based on same template?