Paul has attended:
Excel Advanced course
Duplicating cell values in new columns
Hi,
I'm building a room allocation spreadsheet. Some of the data I'm entering is replicated across a number of time slots (columns). I would like, therefore, for certain information to be replicated in a number of different columns.
Is it possible for the data I enter in column C to immediately replicate in columns E, G, I, K and M? I want to be able to do this without simply copying and pasting because the data in column C is liable to change and I would like it to update automatically in the other columns in real time.
Note that I cannot use a formula in cells E, G, I, K and M as I want to be able to add date to these as I go.
Hope that makes sense,
Paul
RE: Duplicating cell values in new columns
Hello Paul,
Thanks for your interesting question. If you don't want to use linking formulas, you could also record a macro which selects and copies the contents of column C, then pastes it in the other columns. You could then create a button on your spreadsheet which runs the macro and updates the changes in column C to the other columns. While recording the macro, when selecting column C's data, use Ctrl + Shift + Down from the top of the column to capture all the data automatically (there should be no empty cells). This way it allows for always selecting all the records in column C.
Just remember that you can't undo after running a macro, so make a backup copy beforehand.
To create a button, in the Insert ribbon, click Shapes, then draw the button. Right-click the button and assign the macro. Also remember to save the workbook as a macro-enabled workbook.
I hope this helps.
Kind regards
Marius Barnard
Excel Trainer
RE: Duplicating cell values in new columns
Thank you Marius for your reply. Unfortunately it doesn't completely work on my spreadsheet. The problem is that it's only some of the content in one column to be copied to the others. A macro in column C would work if there was a way of running it without writing over cells that are already filled in in columns E, G and I, K and M.
Indeed, my goal is to easily know which rooms are always taken and, therefore, which ones are free. I'm hoping there is another way for all the filled cells in C to be reflected in these other columns, and for blank cells to be left that way.
It is quite an obscure request and it may well be the case that there's no solution. If so, thank you very much for your efforts nonetheless.
Best,
Paul
RE: Duplicating cell values in new columns
Hi Paul,
The only other solution I can think of apart from functions would be some VBA coding which would need to be written, and which can look for specified contents in cells (or blank cells) and based on what it finds, enter data into other cells or leave cells blank.
Sorry I can't be more helpful.
Kind regards
Marius
