Forum Discussion
Update Tab References when Dragging Formulas to Adjacent Cells
I have a workbook that I take month performance data using a separate sheet (tab) for each month with a summary tab to sum the data and use for graphs. The problem is that I think or something to add and I have to adjust the monthly sheets to include this data and often have to move some preexisting data to other cells.
The problem is that now I have to change some the cell references in the summary sheet, which consists of a column for each month. Generally I would put the formula in the first column and drag it across the other 11 columns and the cell references in the formula update accordingly. But the sheet reference does not update so I have to manually change it. I might have over 100 formulas that need the sheet reference changed. Is there any way to make it so the sheet reference will automatically update as well. My column headers match the sheet names on the tabs.
Any insight would be greatly appreciated.
Thank you,
Rob
6 Replies
Rob, in addition,
If your months sheets have exactly the same structure and the same as for template, you may name your resulting cells as
In Name Manager it looks like
In general you could name them in any one month sheet, not necessary in Template.
In Data Summary sheet in title for months better to use any date within the month, drag to the right with Fill Months and format as "mmm".
Next, in summary row for Parts Records
use formula
=INDIRECT(ADDRESS(ROW(PartsRecords),COLUMN(PartsRecords),1,1,TEXT(D$1,"mmm yyyy")))and drag it to the right expanding on all months.
Similar for other records, I guess it will be about ten records.
With that, changing months layout, you need only adjust references for named values. Or do nothing if you change layout by adding/removing rows or columns, if so referenced shall be adjusted automatically. The only point is to have the same layout for all months.
SergeiBaklanThank you Sergei. I will have to play around with that but I definitely like this option.
You'll find the answers to your question here:
https://exceljet.net/formula/dynamic-worksheet-reference
Riny_van_EekelenThank you Riny. I will give this a try.
- The sheet references won’t change when you drag a formula, however you can use find / replace to update with another sheet name once formulae are duplicated (but you will need to do this for each column of formulae:
Highlight the column to update, hit CTRL+H, enter the sheet name in the duplicated formula, e.g. January, into the ’find’ field; enter the sheet name that it should be linked to, e.g. February, into the ‘replace’ field; then click the replace all button.
