Problem with displaying cells containing text and numbers

BlissC

New Member
Joined
Aug 28, 2017
Messages
44
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:

ABCDEF
1General repairsStairliftsWindow contract 2017Door contract123456
28/1/185:002:307:451:309:15
39/1/182:301:003:001:003:15
410/1/181:000:451:156:302:10
511/1/186:151:301:101:300:45

<tbody>
</tbody>


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

AB
ProjectCategory
1General repairsRepairs & servicing
2StairliftsRepairs & servicing
3
4Door contractCapital programmes
5123456Generic 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!
 
Last edited:

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
You've checked cell validation, carriage returns, extra spaces, merged cells, conditional formats?
 
Last edited:

DDRA Steampunk

New Member
Joined
Feb 10, 2017
Messages
20
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:

BlissC

New Member
Joined
Aug 28, 2017
Messages
44
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
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

New Member
Joined
Aug 28, 2017
Messages
44
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:

Watch MrExcel Video

Forum statistics

Threads
1,123,472
Messages
5,601,854
Members
414,479
Latest member
Beau the dog

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
Top