Formula will copy to cells above but not below.....

[Victorthesecond]

Hi, perhaps someone might be able to help me....

I have had to replace an existing formula following some changes I have made. I started by adding the new formula into the appropriate cell on the next blank row in an existing spreadsheet. I then tried sample data in the same row, to prove that the formula worked.

Rows above already contain a formula, and I have been able to "drag" the cell contents (ie the formula) to existing cells ABOVE the row on which the new formula has been entered.

But, when I try and drag the new formula down into empty cells on the rows below where it has been entered, it comes up with "FALSE" in each cell UNLESS I populate the cell that contains the data validation from which the formula draws the word "blah". If I drag the formula down WITHOUT populating the data validation cell, I get the return "FALSE"

Please note that the word "blah" - for the purposes of this thread - has replaced the "proper" data as I need to retain some confidentiality - the "real" data consists of the names of some businesses I work with. In all cases, the "blah" words are captured from a list elsewhere in the spreadsheet. In each row, the word "blah" is captured via Data, Validation from a range of cells which is $A$177:$A$183 and I have made sure that the cells in the data validation column have all been amended to reflect the additional entry I have made in cell A183

The formula I have succesfully entered (and proved works by entering data into the appropriate cells) is:

=IF(O34="blah",(150),IF(O34="blah1",(T34*20)/100,IF(O34="blah2",(100),IF(O34="blah3",(T34*15)/100,IF(O34="Customer own solution","",IF(O34="FCC %age Deal",(150),IF(O34="blah4",(150))))))))

If I drag this into the row below, it comes across like this:

=IF(O35="blah",(150),IF(O35="blah1",(T35*20)/100,IF(O35="blah2",(100),IF(O35="blah3",(T35*15)/100,IF(O35="Customer own solution","",IF(O35="blah %age Deal",(150),IF(O35="blah4",(150))))))))

I can't see any reason why it won't copy downwards!!! There is no difference other than the expected changes to cell numbers and given that the formula gets data from cells in the row and not from elesewhere in the spreadsheet, I'd expect that!

Help!!

All replies very gratefully received!

Thanks

[AliGW]

Please attach a sample spreadsheet.

[newdoverman]

I entered your first formula into a cell on row 34 and made the entries in O34 and T34. I then copied the formula both upwards and downwards (with appropriate data in columns O and T) and it worked flawlessly....no problem at all.

[Victorthesecond]

Thanks for replies....Utterly bizarrely, it all seems OK now. I have re-done the data validation thing across all cells where forced data entry is required and picked a cell at random, lower down the apreadsheet to out the forumla in and try some sample data. That worked fine. I tried putting a few forced entries into the row to make sure that all was well.

Then deleted that and tried copying things down again......and it has worked this time!

Aaaaaaargh!!!!

Anyway, thanks again for taking the time to reply, I really appreciate it.