Problem with displaying cells containing text and numbers

[BlissC]

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:

	A	B	C	D	E	F
1		General repairs	Stairlifts	Window contract 2017	Door contract	123456
2	8/1/18	5:00	2:30	7:45	1:30	9:15
3	9/1/18	2:30	1:00	3:00	1:00	3:15
4	10/1/18	1:00	0:45	1:15	6:30	2:10
5	11/1/18	6:15	1:30	1:10	1:30	0:45

<tbody>
</tbody>

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

	A	B
	Project	Category
1	General repairs	Repairs & servicing
2	Stairlifts	Repairs & servicing
3
4	Door contract	Capital programmes
5	123456	Generic activities

<tbody>
</tbody>

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!

 

[sheetspread]

You've checked cell validation, carriage returns, extra spaces, merged cells, conditional formats?

 

[DDRA Steampunk]

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.

 

[BlissC]

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.

 

[sheetspread]

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?

 

[BlissC]

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.