Problem with displaying cells containing text and numbers

BlissC

New Member
Joined
Aug 28, 2017
Messages
47
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I'm having problems getting the contents of a cell to display where there's text and numbers in the cell. It's a problem that I've never come across before, and I've no idea what the problem is.

I have a table with column headings that I need to display in another table. Simply using =K4, L4, M4, N4 etc., where these are the column headings, in the second table to get them to display in the table seemed to be working fine, until I noticed that not all the column headings were displaying in the second table. I've been trying to figure out why for hours now, and finally noticed that column headings containing both text and numbers are the ones that aren't displaying. The column headings are the names of projects that users are working on in a spreadsheet that calculates the amount of time they work on each project for each month. Users enter the project names themselves, and it's highly likely they could be working on projects specific to a financial year or spanning two years, so column headings that don't display are a real problem.

The first table's set up in the following format:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]General repairs[/TD]
[TD]Stairlifts[/TD]
[TD]Window contract 2017[/TD]
[TD]Door contract[/TD]
[TD]123456[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8/1/18[/TD]
[TD]5:00[/TD]
[TD]2:30[/TD]
[TD]7:45[/TD]
[TD]1:30[/TD]
[TD]9:15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9/1/18[/TD]
[TD]2:30[/TD]
[TD]1:00[/TD]
[TD]3:00[/TD]
[TD]1:00[/TD]
[TD]3:15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10/1/18[/TD]
[TD]1:00[/TD]
[TD]0:45[/TD]
[TD]1:15[/TD]
[TD]6:30[/TD]
[TD]2:10[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]11/1/18[/TD]
[TD]6:15[/TD]
[TD]1:30[/TD]
[TD]1:10[/TD]
[TD]1:30[/TD]
[TD]0:45[/TD]
[/TR]
</tbody>[/TABLE]


This is what displays in the second table though if I use the cell references =A1, =B1, =C1, =D1, etc.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Project[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]General repairs[/TD]
[TD]Repairs & servicing[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Stairlifts[/TD]
[TD]Repairs & servicing[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Door contract[/TD]
[TD]Capital programmes[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]123456[/TD]
[TD]Generic activities[/TD]
[/TR]
</tbody>[/TABLE]


I've tried changing the format of the cells to 'text' rather than 'general' but that makes no difference. Text on it's own displays fine, and numbers on their own display fine, but where the column heading's something like 'Window contract 2017' the cell is left blank.

I've never encountered this behaviour before, and I'm at a loss as to what might be causing it as it's not as though I'm trying to do any calculations or anything with the contents of the cells - I just need to display them!

Any guidance gratefully received!
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
You've checked cell validation, carriage returns, extra spaces, merged cells, conditional formats?
 
Last edited:
Upvote 0
Try using =CELL("contents",'Your Reference Page Name goes here'!A1). It will force Excel to return the exact displayed value of the cell on the reference page you call to.

You've probably already tired these:
1. Is the source cell formatted as text?
2. Is the column displaying the result wide enough?

P.S. I would love to know if this works for you or if it's some other kind of error.
 
Last edited:
Upvote 0
Thanks for the suggestion. Unfortunately that's not worked either, though it's a useful function to know.

All of the cells, both the source cells and the cells I'm trying to populate, are formatted as text, though I've tried various formats with no luck either. The column displaying rhe result is plenty wide enough - there are projects with much longer titles displaying fine. I've tried other project names with both text and numbers, and it's only those that are not showing. I can't work it out - it's not something I've come across before. When I do find a solution though I'll post the answer.
 
Upvote 0
The same problem happens in other cells, or just that one? Check that there's no custom format either. Is this data entered directly or downloaded?
 
Upvote 0
Sorry, I feel like such an idiot! I've just double-checked the conditional formatting on the table, and the column adjacent to the column that displays the project titles has conditional formatting. The adjacent column uses a VLOOKUP to populate it, and so that if it doesn't return a value and shows '0' if the cell contained a '0' I'd set the font colour to white so that it doesn't show. I must have inadvertently applied it to the other column as well.


I've removed the conditional formatting and everything's working fine now. Thanks for all the suggestions guys.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

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