• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing year.

A

aratrika

New Member
I have an Excel worksheet(say sheet 1)consisting with month wise salary (apr-Mar)and against that month wise P.Tax which is fetching it's data from an another worksheet named as 2011-12. Now I want to create an another similar worksheet(say sheet 2) which will fetch it's data from the worksheet 2012-13. I have copied sheet 1 and paste to sheet 2 but I need to change the data for all the cell from 2011-12 to 2012-13. I tried to change by replacing data 2011-12 with 2012-13 but it is changing only one cell at a time. Could any body help me to a better option.


Thanks & regards


Aratrika
 
Hi ,


Can you try the following :


1. Select the sheet "Sheet1" and the sheet "2011-12" and copy them both to a new book.


2. In the new book , change the sheet labelled "Sheet1" to "Sheet2" and the sheet labelled "2011-12" to "2012-13" ; all the formulae will be changed accordingly.


3. Copy these two sheets back to your original workbook.


Narayan
 
Thanks NARAYANAK991 for your valuable feed back but I must inform you that the worksheet 2011-12 is not just a sheet but consisting with 12 sheet namely apr, may and on like that till march. Sheet 1 is fetching data from all the monthly sheet of 2011-12. Therefore it seems copy of the worksheet and renamed as you told is not solving my problem.


Thanks & regards

Aratrika
 
HI aratrika,


You can use
Code: 
INDIRECT(ADDRESS(Row,Column,,,[Sheet Text]))
for this purpose. Assuming that the layout of all the sheets is same, you can link 'Sheet Text' to cell that contains sheet name you want to fetch data from. This will do the trick for you. Just copy the sheet1 and change the 'Sheet Text' to 'Sheet2' and its done. Since you said that there are twelve sheets, one for each month, this implies that there must be twelve more of them for the next year, hence you copy the Sheet1, and toggle the link to sheets for next year using above mentioned formula.


Regards,

Faseeh
 
Hi Faseeh,


Thanks for your interest in the matter but could you please elaborate the same in step by step method.


Look forward to your early response.


Thanks & regards


Aratrika
 
Hi aratrika,


Please see the attached file. What i did was that i created Jan, 12 & Feb, 12 Sheets and their summary, then i copied the three file and by replaced Jan, 12, Feb 12 with respective months in 2013. Try out with 2014 and see whether it works or not.


http://dl.dropbox.com/u/60644346/aratrika_sample_workout.xlsx


Regards,

Faseeh
 
Hi Faseeh,


My problem is little different. Let me explain as under.


1. I have an excel file named "P.Tax Annual" which is consisting with sheets per year such as 2011-12. The sheet 2011-12 is consisting with name of the employees and their respective monthly income and monthly P.Tax as against their salary.


2. Sheet 2011-12 of File "P.Tax Annual" is fetching it's data from an another file called "Salary sheet 2011-12" which is consisting with 12 monthly sheet such as Apr, May and so on upto March and all this monthly sheets are consisting with monthly salary details for that particular year and all this monthly data is transferred to respective monthly column of Sheet 2011-12 of "P.Tax annual" file.


3. Now i create a similar sheet for the year 2012-13 in the "P.Tax Annual" and copied the sheet 2010-11 to sheet 2011-12 and now i need to change the formula from 2011-12 to 2012-13 in the sheet 2012-13 in a shortest possible way. Hope now you will be able to find a solution.


Thanks & regards


Aratrika
 
Hi aratrika,


Can you give me the layout of the sheet? Are the names of employees same through the 12 sheets mean there is no need to match and then fetch salaries of an employee?? It will be lot easier if you gave me the layout of the sheet!!


Regards,

Faseeh
 
Hi, aratrika!

More than giving the sheet's layout consider uploading a sample file to help others in helping you. Refer to the three green sticky posts at this forums main page for guidelines.

Regards!
 
Dear Faseeh & SirJB7,

Sorry i am late.I enclose herewith two links which are self explanatory.Every month i used to process salary in a different worksheet and copy them and save them in Salary Sheet 11-12 and on and on. Some more related files are created which are fetching their data from this Salary sheet file and one of which is P.Tax annual. Now i would like to create an another Salary sheet for the year 12-13 and i want P.Tax for the year 12-13 to fetch its data from that salary sheet as it has done in the year 2011-12. Now i create a similar sheet for the year 2012-13 in the "P.Tax Annual" and copied the sheet 2011-12 to sheet 2012-13 and now i need to change the formula from 2011-12 to 2012-13 in a shortest possible way. Hope now you will be able to find a solution.


Thanks & regards


Aratrika
 
Hi All ,


The correct links are as follows :


1. http://dl.dropbox.com/u/71409773/Salary%20sheet%2011-%2012.xls


2. http://dl.dropbox.com/u/71409773/Ptax%20annual.xls


Narayan
 
Hi aratrika!!


Here are the three file i worked out for you:


http://dl.dropbox.com/u/60644346/Copy%20of%20Ptax%20annual.xlsx

http://dl.dropbox.com/u/60644346/Copy%20of%20Salary%20sheet%2011-%2012.xls

http://dl.dropbox.com/u/60644346/Copy%20of%20Salary%20sheet%2012-%2013.xls


What you need to do:


1. Open Ptax annual. xlsx


2. Open what ever salary sheet you want to open. (In order to elaborate process i have created Salary Sheet 2012-2013 as well).


3. In Ptax annual. xlsx go the the cells C5 & P5 and enter the name of the sheet as you can see it already entered in the cells, you can change it to lets say 2012-2013.


4. You will see that sheet will be updated automatically.


5. In order to add a new sheets, just add one and enter the name of the newly created salary sheet in the cells C5 & P5 (as you already described that you will add a sheet to Ptax annual only when you will have a new salary sheet)


6. That all its done!


NB:

You can link the cells C5 & P5 at your convenience.

I have deleted the extra lines in the Salary sheet that were present. Please note.


If there is any problem fell free to ask.


Regards,

Faseeh
 
Thanks Faseeh it's working. Could you please explain the logic of the formula so that in future i can also use the same where needed.


Thanks & regards


Aratrika
 
You must log in or register to reply here.
Back
Top