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

16 Replies

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).




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 SpreadsheetFirst Part of the Spreadsheet


Second Part of the SpreadsheetSecond Part of the Spreadsheet


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.




best response confirmed by George Carlisle (Contributor)

@George Carlisle 

Check the data on the daily sheets.


 Apart from that the model can be simplified.


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?

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.

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




@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?


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?

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

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

@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.

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.

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

@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).


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.

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