Displaying cell values containing text and numbers in Excel

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. The ones that won't display are where column headings contain both text and numbers.

This is a screenshot of part of my source table: source table showing column headings

This is what displays in the second table though if I use the cell references =K4, =L4, =M4, =N4, etc. As you can see, the cell that should display 'Doors contract 2017'is empty. table where results should show

I've tried changing the format of the cells to 'text' rather than 'general' for all the relevant tables, 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 also tried using =CELL("contents",'Period 1'!K4) etc. rather than just =K4 to try and force Excel to display the exact contents of the column-headings, but with the same result.

Anyone got any idea what might be wrong here?

2 answers

  • answered 2018-01-13 20:30 Relajadito

    have you tried: CTRL +1 (cell format) then "alignment" and check "text adjust" ?

    (I'm not using english excel, but spanish, so maybe the options I describe are not right)

  • answered 2018-01-13 21:14 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.