FORMATING DIFFERENT PAGES IN EXCEL?

Question

The page that I am formatting is page 4 of 4. What I am trying to do with this page is: 
When I enter a number on row 23 - H on sheet ONE,  I need to have this number transposed to sheet 4 row 19 - L.  
The sheets 1, 2, and 3 get updated monthly so the number row 23 -H changes.
Sheet 4 needs to retain the original number that was transposed to it and any future changes to 23-H need to be placed in the same column but to another row if possible.
Thanks for your time,

Anonymous · Accepted Answer

Sounds like you want to create a history that will keep month to month info in a list in a column (or columns) on sheet 4.

Linking 19-L on sheet 4 to 23-H on sheet 1 will only create a link.  Every time 23-H on sheet 1 changes, so will 19-L on sheet 4.

Simply linking 19-L on sheet 4 to 23-H on Sheet 1 will not give you what you want.  Now, if you "save as" a new copy of the spreadsheet at the beginning of each month, then you could go back and find a prior months historical information by opening up one of the older copies of the spreadsheet.  Of course, in 2 years you'd have 24 old files on your hard drive and in 5 years you'd have 60.

If you want to have a continuous month-to-month history kept on sheet 4, one option is simply to do it manually by copying the desired info and then "paste, special, values" to the next available cell in the appropriate column on sheet 4.  Not a lot of effort, but it's also not the "slickest" way of doing it.

Now, If you want that history on (sheet 4) to be saved "automaticaly" then you're going to need to have someone help you create some VBA code that will basically do the following:
(1) Go to sheet 1, move to the cell that needs copying, then copy the info that's in that cell.
(2) Switch to sheet 4.
(3) On sheet 4, go to the column that stores sheet 1 history.
(4) Find the next available blank cell in the sheet 1 history column.
(5) Paste - Special - Values

If sheets 2 and 3 each have info that needs to be copied to sheet 4 for history, then you'll need to have enough VBA code written to handle all that needs to be copied and to put it in the next available cell in the appropriate history columns on sheet 4.

I have shared with you the basic "logic" of what the VBA programming code would need to do, but I am not proficient in VBA code so can't take you any further.

I'm just laying out for you what's possible, and what's needed.

Good Luck

brayden · Answer

The formula in cell L19 on sheet 4 needs to be:

=Sheet1!H23

in order to pick up the number from cell H23 on Sheet 1

Trending News

FORMATING DIFFERENT PAGES IN EXCEL?

2 Answers