Assume a worksheet with formulas referring to the previous worksheet. When this sheet is copied by right clicking the sheet and selecting Move or Copy > Copy, formulas in this newly created sheet, should change to refer to the previous sheet.
In other words, if sheet2 has formulas referring to sheet1, then when sheet3 is created (by Move or Copy > Copy of sheet2), the formulas in sheet3 should refer to sheet2.
You may refer to my solution and Instructions in this workbook.
I am trying to copy this formula to my original workbook beginning with JAN 2014 and I can not get the formula to populate Grand Totals to J37:39
Here is my original
Hi,
You may refer to my solution in this workbook. Please note the following:
1. Ensure that the table which shows the summary of every worksheet is in range E39:F41. I have done this for the first four worksheets. Please do the same for the rest.
2. Copy range I39:L43 of the FEB 2014 worksheet and paste it in cell I39 of each worksheet. I have done this till APR 2014
Hope this helps.
Very Much !! Thank you so much Ashish and also for your patience with me
You are welcome.
I have formated my table to include a cell referencing the current tab name and another cell referencing the previous tab name.
From there I have a table of products all with pricing. My goal is to reference the previous tab to update the table with a change in pricing column.
I’m using the following formula.
=+C5-IFERROR(INDIRECT(“‘”&$D$2&”‘!C5″),””)
Because the “C5” referencing the previous tab is within the “” it is propagating down the list as C5 and not changing to C6… C7… C8 and so on.
Is there a way to get the formula to update down the column?
Thank you.
Hi,
Assuming you are writing that formula in cell row 17 (any column), try this
=C5-IFERROR(INDIRECT(“‘”&$D$2&”‘!C”&ROW()-12),””)
http://excelenthusiasts.sharepoint.com/Documents/Refer%20neighbouring%20sheet.xlsm
Does this also works with the vlookup function iso a certain cell ?
Hi,
Please clarify your question.
Hello, Ashish Mathur!
I’ve just learned about your website and used this set of formulas showed in this article!
Amazing job!
Thank you so much!
You are most welcome. Thank you for your kind words.
Hello. I developed a workbook with a worksheet for each pay period of a year and I want my formulas to pick up the cells in the prior month each time. When I copy/paste, it picks up the first sheet only (as an absolute) and not the relative sheet. How do I write the formula to pick up relative sheet and cell?
Hi,
Please follow the instructions in the Blog article.
I see where this question has been asked numerous times, but I don’t see the correct answer to the exact question. I have 31 pages of Resident Logs – one for each day they sign out. How can I put the correct day and date of each page throughout the month. ie. Page 1 would be Friday, January 1; Page 2 would be Saturday, January 2; Page 3 would be Sunday, January ; etc. I have to use this formula on numerous documents and it sure would make my life easier if I didn’t have to go page by page changing the day and date.
Hi,
I am not clear about your requirement. Please share more details.
Worksheet formulas in a newly copied worksheet should point to the previous worksheet
{ 15 Comments }