Office365 / US English ->I get blank cells when I program formulas to pull values from other sheets

[George Carlisle]

Office 365 on Windows 10 Details:

[#2]

Here's my issue, I have marked the cells that don't populate. If you can't read it, then you need to download and magnify the image).

[#3]

This is the same sheet with the formulas reveals so you can see that my formulas are correct.
I have frozen the first column and have broken the views (revealed formulas) into two screenshots.

First Part of the Spreadsheet

Second Part of the Spreadsheet

[#4]

Problem Summary:

This spreadsheet summarizes data from 31 sheets that are in the same workbook.

Most of the fields functioned correctly and copied blank cells as zeroes in the summary spreadsheet. A few of the fields were unable to do this despite me recreating and recalculating the summary sheet 3 times.  Also, I have validated my formulas again and the proof they are correct is in the screenshots above. I believe this is a bug and I need Microsoft to assign an expert and record this for bug testing. I will attached the complete spreadsheet here zipped for further inspection.

[#5 · Accepted solution]

@George Carlisle 

Check the data on the daily sheets.

=LEN(B23)>0

 Apart from that the model can be simplified.

[#6]

Let's solve the problem first then we'll talk about simplification of the spreadsheet.

Why are some of the fields populating and other's are not?

[#7]

About this comment..."check data on the daily sheets" There is no data. The screenshots I posted "PULL" data from the other sheets. I can post all 31 sheets and location but the formulas tell you where the data is pulled from.  Also, I have already gone to those sheets and cleared, put fake numbers and results still the same when back to blank.

[#8]

Just FYI...this is the same for every sheet...

[#9]

@George Carlisle 

*

---

@George Carlisle wrote:

About this comment..."check data on the daily sheets" There is no data. The screenshots I posted "PULL" data from the other sheets.

I'm referring to the file you uploaded. The daily sheets contain data.

Did you put the formula in D23:D46 on every sheet and do the results make sense for you?

[#10]

I have no idea where this comment is coming from, Detief. There are 31 sheets with daily data.

Your first comment about Len I have no idea why you suggested that. The column I pull data from is number not characters. Let me add some details here for you.

The daily track blood readings from 0 hours - 23 hours daily. The Summary sheet pulls that data of when the readings happens and puts them in the summary sheet. Why is everyone making this more difficult this should be?

[#11]

The field reads from B23:B46 in each daily sheet, Detlef. 

Can you stop commenting on my posts...Please?

[#12]

@George Carlisle 

@Detlef Lewin  is correct. You have spaces in your cells not blanks.  Go to ='Day19'!B23 and clear contents then check your summary sheet again.

[#13]

How are clearing the cells? Before I submit this to the forum, I did a manual clear (touched cell and deleted out for manually for 5 seconds, then later I did clear contents right clicking the cell. Finally, I recreated the sheet and did days sheets completely new and rebuild the whole spreadsheets.

[#14]

Before we go any further, post details about your excel version (win/mac, excel version, on-premise or click2run(c2k).

[#15]

@George Carlisle 

The version doesn't matter in this case.  Here are the results of LEN on the Day19 sheet.

You can clear any spaces in A23:A46 in all the sheets by grouping the sheets, performing a find/replace to locate spaces and replace with nothing (leave replace blank).

[#16]

I see now...Len will let you look at a cell and see into it. I didn't know it could see numbers. I thought it could only see letters. When I used the clear content on the cells the zeroes appeared in the summary. The only way a space could be there is maybe I "spaced / spacebar" out a digit I planted to test but I guess spacing creates a character even though I thought I removed it.

[#17]

Thanks both of your for your help today. This problem vexed me for 1 week and I lost a lot of time trying to figure this out. I thought it was a bug.

Sorry for the comments about your competency, @Detlef Lewin
I won't make that mistake again....

Thanks for your assistance too @Patrick2788