Home

Auto updating formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-768419%22%20slang%3D%22en-US%22%3EAuto%20updating%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-768419%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20have%20formulas%20auto%20populate.%20%26nbsp%3BExample%20would%20be%20a%20hyperlink.%20%26nbsp%3BLet's%20say%20the%20formula%20is%20%3DHYPERLINK(%22C%3A%5CUsers%5CDocuments%5CFolder%20101%22%2CName).%20I%20need%20to%20make%20like%20300%20of%20these.%20%26nbsp%3BHow%20can%20I%20auto%20update%20the%20%22101%22%20section%20of%20the%20code%20to%20102%2C%20103%2C%20104%2C%20etc.%20without%20typing%20them%20all%20out%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-768419%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-768436%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20updating%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-768436%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380356%22%20target%3D%22_blank%22%3E%40Budman36%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20make%20a%20helper%20column%20in%20Column%20B%20with%20numbers%20101%2C%20102%2C%20103%2C%20etc.%20listed%20and%20use%20this%20formula%20in%20cell%20A1%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DHYPERLINK(%22C%3A%5CUSERS%5CDOCUMENTS%5CFOLDER%20%22%20%26amp%3B%20B1%20%26amp%3B%20%22%2CName%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20simply%20copy%20the%20formula%20down%20to%20the%20last%20cell%20that%20contains%20a%20number%20in%20Column%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-768898%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20updating%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-768898%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380356%22%20target%3D%22_blank%22%3E%40Budman36%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20hyperlink%20starts%20in%20A2%2C%20and%20you%20want%20to%20avoid%20a%20helper%20column%2C%20you%20may%20instead%20try%20this%20formula%2C%20copied%20down%20rows%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DHYPERLINK(%3CSPAN%3E%22C%3A%5CUsers%5CDocuments%5CFolder%20%22%26amp%3BROW()-%3CSTRONG%3E2%3C%2FSTRONG%3E%2B101%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EName)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20%22%3CSTRONG%3E2%3C%2FSTRONG%3E%22in%20the%20foregoing%20pertains%20to%20the%20starting%20row%20of%20the%20formula%2C%20which%20is%20A%3CSTRONG%3E2%3C%2FSTRONG%3E.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-770076%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20updating%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770076%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EThis%20worked%20perfect!%20%26nbsp%3BThank%20you%20so%20much%20for%20your%20help.%20%26nbsp%3BYou%20saved%20an%20extreme%20amount%20of%20frustration!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Budman36
Contributor

Is it possible to have formulas auto populate.  Example would be a hyperlink.  Let's say the formula is =HYPERLINK("C:\Users\Documents\Folder 101",Name). I need to make like 300 of these.  How can I auto update the "101" section of the code to 102, 103, 104, etc. without typing them all out?

3 Replies

Hello @Budman36,

 

You could make a helper column in Column B with numbers 101, 102, 103, etc. listed and use this formula in cell A1:

 

=HYPERLINK("C:\USERS\DOCUMENTS\FOLDER " & B1 & ",Name")

 

Then simply copy the formula down to the last cell that contains a number in Column B.

 

Hope this helps!

PReagan

@Budman36 

If your hyperlink starts in A2, and you want to avoid a helper column, you may instead try this formula, copied down rows: 

=HYPERLINK("C:\Users\Documents\Folder "&ROW()-2+101,

Name)

The "2"in the foregoing pertains to the starting row of the formula, which is A2

Hey @PReagan,

This worked perfect!  Thank you so much for your help.  You saved an extreme amount of frustration!

 

 

 

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies