Item formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3338998%22%20slang%3D%22en-US%22%3EItem%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3338998%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20enter%20an%20Item%20so%20each%20time%20a%20corresponding%20%24%20amount%20is%20put%20into%20a%20cell%20in%20the%20same%20row%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3338998%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-3339025%22%20slang%3D%22en-US%22%3ERe%3A%20Item%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383953%22%20target%3D%22_blank%22%3E%40dinothekiwi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20cell%20where%20you%20want%20the%20amount%20to%20appear%2C%20you%20enter%20a%20formula%20like%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(A1%3D%22desired%20entry%22%2C45.67%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethere%20are%20other%20ways%2C%20more%20sophisticated%20ways%2C%20if%20you%20actually%20have%2C%20say%2C%2020%20different%20possible%20entries%20for%20cells%20in%20column%20A%2C%20each%20with%20its%20associated%20dollar%20amount.%20Then%20you%20could%20use%20VLOOKUP%20and%20a%20table%20with%20the%20text%20entries%20and%20the%20associated%20dollar%20amounts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20you%20might%20want%20to%20give%20a%20more%20complete%20description%20here%20of%20what%20the%20bigger%20picture%20is%2C%20so%20a%20more%20robust%20and%20reliable%20answer%20could%20be%20created.%20What%20I've%20given%20you%20will%20work%2C%20but%20depending%20on%20the%20situation%2C%20on%20the%20importance%20of%20it%2C%20you%20want%20to%20make%20sure%20that%20there%20are%20no%20%22holes%22%20in%20the%20logic%20or%20design.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3339028%22%20slang%3D%22en-US%22%3ERe%3A%20Item%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20very%20helpful%2C%20much%20appreciated.%3C%2FP%3E%3CP%3EI%20often%20make%20tables%20to%20track%20my%20Building%20jobs.%20There%20are%20some%20Items%20that%20have%20fixed%20prices%2Fcosts%20and%20although%20the%20Item%20is%20quick%20to%20enter%20it's%20time%20consuming%20also%20having%20to%20input%20the%20amount%20each%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3339029%22%20slang%3D%22en-US%22%3ERe%3A%20Item%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339029%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383953%22%20target%3D%22_blank%22%3E%40dinothekiwi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20that%20case%2C%20there%20are%20two%20things%20you%20should%20dig%20into.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EData%20Validation%20(which%20would%20give%20you%20a%20drop-down%20list%20for%20the%20items)%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-data-validation-guide%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-data-validation-guide%3C%2FA%3E%3C%2FLI%3E%3CLI%3EVLOOKUP%20to%20enable%20you%20to%20access%20a%20table%20with%20the%20various%20entries%20and%20their%20prices%20%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-vlookup-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-vlookup-function%3C%2FA%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3339049%22%20slang%3D%22en-US%22%3ERe%3A%20Item%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339049%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383953%22%20target%3D%22_blank%22%3E%40dinothekiwi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%3A%20I've%20created%20a%20simple%20example.%20The%20image%20is%20here%3B%20the%20file%20is%20attached%20below.%20%26nbsp%3BYou%20fill%20in%20the%20yellow%20backed%20cells%2C%20the%20rest%20is%20done%20for%20you.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1651972382462.png%22%20style%3D%22width%3A%20572px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369976iEC5D18A2D2BA8322%2Fimage-dimensions%2F572x273%3Fv%3Dv2%22%20width%3D%22572%22%20height%3D%22273%22%20role%3D%22button%22%20title%3D%22mathetes_0-1651972382462.png%22%20alt%3D%22mathetes_0-1651972382462.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3339052%22%20slang%3D%22en-US%22%3ERe%3A%20Item%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339052%22%20slang%3D%22en-US%22%3EThat's%20really%20cool.%20It's%20testing%20my%20Excel%20limits%20though.%3CBR%20%2F%3EI%20will%20need%20to%20study%20it%20more%20if%20I%20want%20to%20replicate.%3CBR%20%2F%3EThanks%20again.%3C%2FLINGO-BODY%3E
New Contributor

How do I enter an Item so each time a corresponding $ amount is put into a cell in the same row?

5 Replies

@dinothekiwi 

 

In the cell where you want the amount to appear, you enter a formula like

=IF(A1="desired entry",45.67,0)

 

there are other ways, more sophisticated ways, if you actually have, say, 20 different possible entries for cells in column A, each with its associated dollar amount. Then you could use VLOOKUP and a table with the text entries and the associated dollar amounts.

 

But you might want to give a more complete description here of what the bigger picture is, so a more robust and reliable answer could be created. What I've given you will work, but depending on the situation, on the importance of it, you want to make sure that there are no "holes" in the logic or design.

@mathetes 

 

That's very helpful, much appreciated.

I often make tables to track my Building jobs. There are some Items that have fixed prices/costs and although the Item is quick to enter it's time consuming also having to input the amount each time.

@dinothekiwi 

 

In that case, there are two things you should dig into.

 

  1. Data Validation (which would give you a drop-down list for the items) https://exceljet.net/excel-data-validation-guide
  2. VLOOKUP to enable you to access a table with the various entries and their prices  https://exceljet.net/excel-functions/excel-vlookup-function

 

 

@dinothekiwi 

 

Here: I've created a simple example. The image is here; the file is attached below.  You fill in the yellow backed cells, the rest is done for you.

mathetes_0-1651972382462.png

 

That's really cool. It's testing my Excel limits though.
I will need to study it more if I want to replicate.
Thanks again.