Valuing some cells but not others

%3CLINGO-SUB%20id%3D%22lingo-sub-989906%22%20slang%3D%22en-US%22%3EValuing%20some%20cells%20but%20not%20others%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-989906%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20%22value%22%20the%20results%20of%20a%20vlookup%20BUT%20in%20the%20same%20column%20not%20value%20the%20%22sum%22%20of%20those%20vlookups.%26nbsp%3B%20Basically%2C%20I%20want%20the%20vlookups%20to%20be%20converted%20to%20values%20(Which%20I%20know%20how%20to%20do)%20but%20not%20value%20the%20results%20of%20the%20sum.%26nbsp%3B%20Also%2C%20I%20am%20trying%20to%20stay%20away%20from%20protect%2Flock%2Funlocking%20of%20cells%20and%20valuing%20specific%20cells%20at%20a%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-989906%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-990046%22%20slang%3D%22en-US%22%3ERe%3A%20Valuing%20some%20cells%20but%20not%20others%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-990046%22%20slang%3D%22en-US%22%3EYour%20description%20is%20a%20bit%20confusing.%20Is%20it%20possible%20for%20you%20to%20attach%20a%20sample%20spreadsheet.%3CBR%20%2F%3E%3CBR%20%2F%3EThere%20is%20a%20function%20that%20might%20do%20what%20you%20want%2C%20that%20being%20%3DTEXT(value%2Cformat)%20which%20you%20could%20use%20in%20that%20SUM%20field%20to%20convert%20whatever%20value%20it%20is%20holding%20(back)%20into%20text.%20But%20as%20I%20said%2C%20it's%20not%20clear%20from%20your%20description%20exactly%20what%20you're%20looking%20at.%20So%20a%20sample%20uploaded%20here%20would%20help%20me%20or%20someone%20else%20solve%20your%20problem.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-990079%22%20slang%3D%22en-US%22%3ERe%3A%20Valuing%20some%20cells%20but%20not%20others%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-990079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F448935%22%20target%3D%22_blank%22%3E%40WendyK518%3C%2FA%3E%26nbsp%3BUnless%20you%20do%20it%20manually%2C%20the%20only%20way%20to%20convert%20formulas%20to%20values%20is%20with%20VBA.%20It's%20certainly%20doable%2C%20but%20you'd%20need%20to%20lay%20out%20your%20process.%20For%20example%2C%20you%20could%20have%20a%20button%20that%20applies%20your%20VLOOKUPs%2C%20recalculates%20the%20sheet%2C%20then%20values%20them.%20If%20that%20sounds%20like%20a%20plan%2C%20then%20I'd%20start%20by%20recording%20a%20macro%20doing%20that.%20You%20can%20always%20post%20the%20code%20back%20here%20for%20someone%20to%20clean%20up.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHope%20that%20helps%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I would like to "value" the results of a vlookup BUT in the same column not value the "sum" of those vlookups.  Basically, I want the vlookups to be converted to values (Which I know how to do) but not value the results of the sum.  Also, I am trying to stay away from protect/lock/unlocking of cells and valuing specific cells at a time.

 

Any help?

2 Replies
Highlighted
Your description is a bit confusing. Is it possible for you to attach a sample spreadsheet.

There is a function that might do what you want, that being =TEXT(value,format) which you could use in that SUM field to convert whatever value it is holding (back) into text. But as I said, it's not clear from your description exactly what you're looking at. So a sample uploaded here would help me or someone else solve your problem.
Highlighted

@WendyK518 Unless you do it manually, the only way to convert formulas to values is with VBA. It's certainly doable, but you'd need to lay out your process. For example, you could have a button that applies your VLOOKUPs, recalculates the sheet, then values them. If that sounds like a plan, then I'd start by recording a macro doing that. You can always post the code back here for someone to clean up.

 

Hope that helps,