Home

Auto Calculate Data

%3CLINGO-SUB%20id%3D%22lingo-sub-693688%22%20slang%3D%22en-US%22%3EAuto%20Calculate%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-693688%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20be%20able%20to%20plug%20in%20a%20specific%20sample%20number%20and%20have%20the%20corresponding%20data%20automatically%20update%20to%20the%20existing%20formulas.%26nbsp%3B%20I've%20uploaded%20a%20mock%20spreadsheet%20to%20assist%20in%20the%20request.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-693688%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-694051%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Calculate%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694051%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360672%22%20target%3D%22_blank%22%3E%40Rebecca560%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20simple%20VLOOKUP%20will%20return%20your%20desired%20results.%20For%20example.%20the%20formula%20in%20C9%20in%20the%20attached%20file%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(C%241%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EDataTable%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EMATCH(A9%2CDataLabels%2C0)%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ENote%20the%20following%20defined%20names%20used%20in%20the%20foregoing%20formula%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EName%3C%2FTD%3E%3CTD%3EFormula%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDataLabels%3C%2FTD%3E%3CTD%3E%3DData!%24A%242%3A%24G%242%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDataTable%3C%2FTD%3E%3CTD%3E%3CP%3E%3DData!%24A%243%3A%24G%247%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Rebecca560
Occasional Visitor

I would like to be able to plug in a specific sample number and have the corresponding data automatically update to the existing formulas.  I've uploaded a mock spreadsheet to assist in the request.  

1 Reply

@Rebecca560 

A simple VLOOKUP will return your desired results. For example. the formula in C9 in the attached file is: 

=VLOOKUP(C$1,
DataTable,
MATCH(A9,DataLabels,0),0)

Note the following defined names used in the foregoing formula: 

NameFormula
DataLabels=Data!$A$2:$G$2
DataTable

=Data!$A$3:$G$7

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies