Copying worksheet (containing formulae & data) to another workbook, to use for the next yr

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
125
Hi

Using Excel 2010 on Windows 10

I have a Workbook for 2018, Sheets are Jan - Dec, ie 12 sheets, plus a sheet containing data, ie named payees (income) and recipients (outgoings), that has been 'entered' from the previous month, plus manually entered too.

There is a 'Master' sheet which takes this above data from Dec 2018 sheet, ie sources re Income and destinations re Outgoings. The formula in 'Master' sheet is, for example "{=Dec!C16}".

I thought I'd copy this 'Master' sheet, which contains data that needs to be continued into Jan sheet of the next year, ie 2019, and paste into a new sheet, named 'Master2018' . Then, in Jan (2019 Workbook) I thought I could use a similiar formula to catch that 2018 data, and have it displayed in the Jan 2019 sheet, then be ready to continue with the 2019 accounts.

So -
- I entered '=' into the first required cell in the Jan 2019 sheet,
- clicked on the relevant cell in the new 'Master 2018' sheet,
- then clicked 'Enter' (also tried Shift+Ctrl+Enter)
- the formula showing was "='[P****Accounts 2018 Rev 8-12-18.xlsm]Dec'!C16"
- but, the data in the cell in Jan (2019) is "='2018'!B6" rather than the data, which should be a name, ie Joe Bloggs.

I've clearly not done something correctly, due to my limited knowledge. I hope you can understand my post / scenario ;)

Could someone please help me and let me know how I can achieve this, please?

I appreciate any help. Thanks very much
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
125
Hi
I was really hoping there was a simple solution to this issue and that someone would post a response
I'm still hopeful someone can help me out with this ..... please ;)
Many thanks
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,598
.
Does the MASTER sheet differ from the JAN sheet ?
 

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
125
.
Does the MASTER sheet differ from the JAN sheet ?

The Master sheet is designed to 'pick up' data from 'December' (2018) sheet, by formula in each cell such as "{=Dec!C22}" to 'pick up' the Payee's Name. By the same method, other data is 'picked up' such as Gift Aid status.

On this Master sheet, I have an 'Income' section, being 3 columns - for Name, ID and Gift Aid Y/N. There are 75 entries (Names/Rows etc) in Income, of which the majority are regular names, hence wanting to minimise manual entry into Jan 2019. Outgoings has a lesser number of names entered but, again, many will be repeated each month.

Jan (2018) sheet doesn't have the above forumula in, as 2018 was the first year I have created the spreadsheet. Feb (2018) to Dec (2018) have the equivalent formulae, ie "{=Dec!C22}", to bring forward the entry from the previous month, ending up in Dec (2018) unless deleted on a month sheet.

I had hoped to be able to select the data from Master (2018) sheet and paste into a new sheet in the 2019 Workbook, with Jan (2019) picking up the data, so as to continue to carry it forward month by month

So, yes, I feel the Master sheet is 'differen't in that the data is 'picked up' using formulae so, when pasted into new sheet in 2019 workbook, all I get is a formula pointing to the 2018 workbook, rather than just the 2019 Mastersheet.

I do hope this makes sense. I'm finding it very difficult to explain.

Many thanks
 

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,200
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I have copied a sheet from previous year for solar statistics
Have tabs with the name year (eg Jan 2019) and a master sheet which collects MTD data from these tabs.
The format for Month name on mastersheet is custom "mmm" and is entered as 1/01/2018
This is what it looks like
Excel Workbook
ACDMNO
3days303131
4SUMMARY OF SOLAR PRODUCTION / USAGE ETCJANFEBNovDecJan
5TARGET PRODUCTION MTD  98210341016
6ACTUAL PRODUCTION MTD  106410601134
SUMMARY PV PRODUCTION USAGE (2)

Just look at formulae in M3:O3 and M6:O6 This is how I get the figures from the tabs for each month.
The month shown in row 4 (M4:O4) are entered as "1/11/18" and formatted as custom"mmm"
You will note the change in year in N6 and O6.
That might give you a bit of a guide.

Pedro
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,598
.
If pedro-egoli example does not provide the information you require for your workbook.

Please post a link to download your workbook (without confidential information of course) so we can see the actual sheets and formulas.
That way we won't need to guess how to proceed and you will obtain the best answer possible.

For posting an example, you can use DropBox, Google Cloud or something similar, then provide the downlink here.

Thanks.
 

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
125

ADVERTISEMENT

Hi guys

Many thanks for trying to help.

pedro-egoli
I really appreciate you taking the trouble to post images of a spreadsheet. It was good of you to spend the time. Unfortunately, your spreadsheet forumlae is much to complicated for me to understand and follow.

Logit
Again, many thanks for your suggestion to upload (to Dropbox) and post the link. I'll bear this in mind in future.

However, I've just had a 'eureka' moment ... So, I've now solved my issue!!! ;)


Something prompted me that there were various options in 'Paste' after Right Click. So, with some experimenting, I've discovered I can do exactly what I want to do by-

Right click on cell where data to be pasted
Hover cursor over 'Paste Special'
Press 'E' on keyboard (Shortcut for 'Paste Values & Source Formatting')
Hey Presto!!!

As I've already mentioned, I am very grateful for all your help. I value all the help available on this forum and is my 'go to' when I have issues using Excel that I can't solve, even after searching the internet.

Thanks guys


 
Last edited:

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
125
Thanks

I just felt there should be a way to copy the data captured so as to paste into a new workbook. It hadn't occured to me there may be the option of doing just that in 'Paste Special' as I rarely do other than Ctrl+C / Ctrl+V ;)

So, something else I've learnt ... ;)

Until next problem, eh ..... ??? ;)

Thanks again

PS How can I mark this thread as SOLVED, please? :)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,348
Messages
5,528,182
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top