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

[PurrSent]

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

 

[PurrSent]

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]

.
Does the MASTER sheet differ from the JAN sheet ?

 

[PurrSent]

.
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]

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
	A	C	D	M	N	O
3	days			30	31	31
4	SUMMARY OF SOLAR PRODUCTION / USAGE ETC	JAN	FEB	Nov	Dec	Jan
5	TARGET PRODUCTION MTD			982	1034	1016
6	ACTUAL PRODUCTION MTD			1064	1060	1134
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]

.
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]

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

​

 

[pedro-egoli]

Glad you solved it.

 

[Logit]

Ditto ... glad you solved it.

Cheers

 

[PurrSent]

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?