Sorry for vague title, couldn’t summarise it well enough…

I have received 30 reports, standardised in 4 chapters. I have to write one big summary report. I’ve used Excel to write brief notes of their contents. So I have one Excel file with four worksheets, titled Chapter A, B, C, and D. Each worksheet has 30 rows, and maybe 10 columns (depending on number of my notes). Each cell has maybe 20 words of text.

Now I find that some of the info written in a report in, say, Ch.A should have been in Ch.B, ie it addresses the subject of Ch.B. There are many like this. I can simply copy the text from worksheet A to worksheet B. BUT I’d like to show that it came from worksheet A – as when I’m finally writing the big report, I’d like to be able to go back and consult the original text in the original report, and I don’t want to be reading through the wrong chapter!

So here I have a load of cells with text in. Is there any way I can run a blanket command on each worksheet to say that all cells (or better, all cells in a defined area) should have any contents preceded by “ChA”, without replacing/erasing whatever text is in there at present?

(I know the really easy way of doing this would be to colour the text in four different colours, but I’m expecting to use colours later for other notes, so am trying to avoid that. I know also I could link the cells between worksheets, but I want to do most of the reading on paper, not on screen, and these wouldn’t show.)

Thanks for any suggestions

Brendan

 

"Insert Comments" ??

 

Only works for individual cells, I can't do a group in one hit

I might not have been clear - sorry I'd like to label all the cells in a sheet BEFORE I start moving them - rather than labelling individual ones AFTER I've moved them. If it was the latter, I just change the text inside each cell after I've moved it.

If I can't do the blanket change, I might stick with the above method, but I was wondering if the former action was feasible... Sort of Replace All, where you choose to replace "*" (any cell contents) with "ChA: *".

 

try the concatenate formula, that should do what you want to do as long as I understand you properly LOL

=concatenate("CHA: ",A1)

 

DB, thanks, it looks close - the only thing is I can't get a cell to change itself (circular ref!), I have to mirror it in a different cell. So I'll have one table of 30x10, then I presumably have to create another table of 30x10 with the concatenated cells.

(Apart from that, I can't work out how to apply it to a range, I can only apply it to one cell at a time...)

 

Yes you would need to have the text already typed before you could add the start bit in another cell.

I would do it as follows:
- Create another 4 tabs called ChA, ChB etc...
- In the new ChA tab enter the concatenate formula in cell A1 (or the top left cell with any data in) then drag it out to cover the 30 rows & 10 columns (using the tiny black square in the bottom right of the cell A1)
- Do the same for ChB, C & D.
- copy and paste as values all the data in the 4 new tabs, they will all have the relevant chapter "ChA:" (etc.) before each piece of text and you can now cut and paste them into any of the 3 other tabs as required.
- You could also delete the original 4 tabs with the pre-edited text in if you wanted, or save it to a seperate spreadsheet.

HTH

 

Done! Didn't start new tabs, just pasted below the original. One problem flummoxed me for 10 mins - the syntax. Both you and Excel Help use a comma as a separator, but the sheet only accepted a semi-colon?

Nevertheless, please accept a virtual beer as thanks

 

Hmm strange, glad you got it sorted though.

Ta for the beverage too LOL