Update Tab References when Dragging Formulas to Adjacent Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1170754%22%20slang%3D%22en-US%22%3EUpdate%20Tab%20References%20when%20Dragging%20Formulas%20to%20Adjacent%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1170754%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20that%20I%20take%20month%20performance%20data%20using%20a%20separate%20sheet%20(tab)%20for%20each%20month%20with%20a%20summary%20tab%20to%20sum%20the%20data%20and%20use%20for%20graphs.%26nbsp%3B%20The%20problem%20is%20that%20I%20think%20or%20something%20to%20add%20and%20I%20have%20to%20adjust%20the%20monthly%20sheets%20to%20include%20this%20data%20and%20often%20have%20to%20move%20some%20preexisting%20data%20to%20other%20cells.%20%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20now%20I%20have%20to%20change%20some%20the%20cell%20references%20in%20the%20summary%20sheet%2C%20which%20consists%20of%20a%20column%20for%20each%20month.%26nbsp%3B%20Generally%20I%20would%20put%20the%20formula%20in%20the%20first%20column%20and%20drag%20it%20across%20the%20other%2011%20columns%20and%20the%20cell%20references%20in%20the%20formula%20update%20accordingly.%26nbsp%3B%20But%20the%20sheet%20reference%20does%20not%20update%20so%20I%20have%20to%20manually%20change%20it.%26nbsp%3B%20I%20might%20have%20over%20100%20formulas%20that%20need%20the%20sheet%20reference%20changed.%26nbsp%3B%20Is%20there%20any%20way%20to%20make%20it%20so%20the%20sheet%20reference%20will%20automatically%20update%20as%20well.%26nbsp%3B%20My%20column%20headers%20match%20the%20sheet%20names%20on%20the%20tabs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20insight%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3ERob%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1170754%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177524%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Tab%20References%20when%20Dragging%20Formulas%20to%20Adjacent%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177524%22%20slang%3D%22en-US%22%3EThe%20sheet%20references%20won%E2%80%99t%20change%20when%20you%20drag%20a%20formula%2C%20however%20you%20can%20use%20find%20%2F%20replace%20to%20update%20with%20another%20sheet%20name%20once%20formulae%20are%20duplicated%20(but%20you%20will%20need%20to%20do%20this%20for%20each%20column%20of%20formulae%3A%3CBR%20%2F%3EHighlight%20the%20column%20to%20update%2C%20hit%20CTRL%2BH%2C%20enter%20the%20sheet%20name%20in%20the%20duplicated%20formula%2C%20e.g.%20January%2C%20into%20the%20%E2%80%99find%E2%80%99%20field%3B%20enter%20the%20sheet%20name%20that%20it%20should%20be%20linked%20to%2C%20e.g.%20February%2C%20into%20the%20%E2%80%98replace%E2%80%99%20field%3B%20then%20click%20the%20replace%20all%20button.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177574%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Tab%20References%20when%20Dragging%20Formulas%20to%20Adjacent%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320791%22%20target%3D%22_blank%22%3E%40PdM_Rob%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou'll%20find%20the%20answers%20to%20your%20question%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fdynamic-worksheet-reference%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fdynamic-worksheet-reference%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177654%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Tab%20References%20when%20Dragging%20Formulas%20to%20Adjacent%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177654%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320791%22%20target%3D%22_blank%22%3E%40PdM_Rob%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERob%2C%20in%20addition%2C%3C%2FP%3E%0A%3CP%3EIf%20your%20months%20sheets%20have%20exactly%20the%20same%20structure%20and%20the%20same%20as%20for%20template%2C%20you%20may%20name%20your%20resulting%20cells%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20328px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171489iB30214FEC0AB11E1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20Name%20Manager%20it%20looks%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20221px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171490iC03118F9E42E87C7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20general%20you%20could%20name%20them%20in%20any%20one%20month%20sheet%2C%20not%20necessary%20in%20Template.%3C%2FP%3E%0A%3CP%3EIn%20Data%20Summary%20sheet%20in%20title%20for%20months%20better%20to%20use%20any%20date%20within%20the%20month%2C%20drag%20to%20the%20right%20with%20Fill%20Months%20and%20format%20as%20%22mmm%22.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20305px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171491i37267144F0F3D8EE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENext%2C%20in%20summary%20row%20for%20Parts%20Records%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20554px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171492iFE86EF694FB1B1F5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Euse%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDIRECT(ADDRESS(ROW(PartsRecords)%2CCOLUMN(PartsRecords)%2C1%2C1%2CTEXT(D%241%2C%22mmm%20yyyy%22)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20to%20the%20right%20expanding%20on%20all%20months.%3C%2FP%3E%0A%3CP%3ESimilar%20for%20other%20records%2C%20I%20guess%20it%20will%20be%20about%20ten%20records.%3C%2FP%3E%0A%3CP%3EWith%20that%2C%20changing%20months%20layout%2C%20you%20need%20only%20adjust%20references%20for%20named%20values.%20Or%20do%20nothing%20if%20you%20change%20layout%20by%20adding%2Fremoving%20rows%20or%20columns%2C%20if%20so%20referenced%20shall%20be%20adjusted%20automatically.%20The%20only%20point%20is%20to%20have%20the%20same%20layout%20for%20all%20months.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177849%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Tab%20References%20when%20Dragging%20Formulas%20to%20Adjacent%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177849%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531239%22%20target%3D%22_blank%22%3E%40Charla74%3C%2FA%3EThank%20you%20Charla.%26nbsp%3B%20That%20will%20work.%26nbsp%3B%20Have%20a%20great%20day.%3C%2FP%3E%3CP%3ERob%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177869%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Tab%20References%20when%20Dragging%20Formulas%20to%20Adjacent%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThank%20you%20Sergei.%26nbsp%3B%20I%20will%20have%20to%20play%20around%20with%20that%20but%20I%20definitely%20like%20this%20option.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177870%22%20slang%3D%22en-US%22%3ERe%3A%20Update%20Tab%20References%20when%20Dragging%20Formulas%20to%20Adjacent%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3EThank%20you%20Riny.%26nbsp%3B%20I%20will%20give%20this%20a%20try.%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@PdM_Rob 

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

image.png

In Name Manager it looks like

image.png

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

image.png

Next, in summary row for Parts Records

image.png

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.

@Charla74Thank you Charla.  That will work.  Have a great day.

Rob 

@Sergei BaklanThank you Sergei.  I will have to play around with that but I definitely like this option.

@Riny_van_EekelenThank you Riny.  I will give this a try.