Formulas Work Only One Line and Not Any Copied Down Below

brenda1996

New Member
Joined
Aug 30, 2018
Messages
29
Hi again,

Looking for some help as to why two formulas I have in my spreadsheet work perfect as intended but anything below that one line I copied down to will not.

IF(AND(C3=365,OR(D3="MON",D3="YR",D3="D")),"SHOULD BE DAY","") and IF(AND(C3<>365,OR(D3="DAY",D3="D")),"SHOULD BE MON or YR","") are in separate columns.

When I play with the data in columns C and D I get the expected results for only that one line. Formulas are the same copied down with the exception of line number.

The only way I can get formulas to work was to copy first line entirely twice below it.

In the snippet the first 3 rows are expected results, everything below is not and each cell was copied down from the first row. :(

The last four rows Columns E and F should both be blank and in first row. The fourth row should be the same as the second row.

Any help is sincerely appreciated as I have run into a dead end and about fixing to start pulling my hair out!

Brenda
 

Attachments

  • Capture.JPG
    Capture.JPG
    50.1 KB · Views: 10

brenda1996

New Member
Joined
Aug 30, 2018
Messages
29
Yes, so on all unexpected results I get FALSE for both tests on Column C and D. Checking the length the the character lengths are correct for the data.
.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,748
Office Version
  1. 365
Platform
  1. Windows
Ok where you see an incorrect result test the cell in C with =ISNUMBER(C5) where 5 needs replacing with the actual row number
 

brenda1996

New Member
Joined
Aug 30, 2018
Messages
29
So is that unexpected? I cant see your sheet dont forget. You should be getting lots of TRUE and FALSE if you are testing the sample you gave. What you are looking for is where you see an unexpected result. Then test that cell with the LEN formula.
Oh, so when I physically type in a number the formulas work. Looks like it is tied to the numbers. Ii did a clean, could it be a format issue?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,748
Office Version
  1. 365
Platform
  1. Windows
Ok so your number looks like its text so add zero to it. So C3+0=365 for example. Should work then.
 
Solution

brenda1996

New Member
Joined
Aug 30, 2018
Messages
29
We'll I'll be! Thank you Steve, you are a genius! I will have to remember that little trick! Thank you so much!!!! Have a wonderful day!
 

Forum statistics

Threads
1,176,081
Messages
5,901,278
Members
434,884
Latest member
ares0027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top