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: 12
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.
.
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Ok where you see an incorrect result test the cell in C with =ISNUMBER(C5) where 5 needs replacing with the actual row number
 
Upvote 0
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?
 
Upvote 0
Ok so your number looks like its text so add zero to it. So C3+0=365 for example. Should work then.
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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
Back
Top