change value in a cell according to another cells value

Please help me....
Example:
I need to make 1 = $50 and 2 = $100 and 3 = $120 etc
I need to do this by entering 1, 2 or 3 in column A and the value 
automatically appearing in column D, is this possible?

Also I would like to hide a column and only show cells from that column in 
the row that I apply value to in another cell of the same row. Essentially 
hiding the whole column and only showing value in a row when the row is in 
use.

Please help me, I want to create something for a job interview and can't 
work it out!

Thank you so much whoever can answer this.
0
KylieRose (3)
1/19/2006 6:28:02 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
2928 Views

Similar Articles

[PageSpeed] 8

"Kylie Rose" wrote:
> .. I need to make 1 = $50 and 2 = $100 and 3 = $120 etc
> I need to do this by entering 1, 2 or 3 in column A and the value
> automatically appearing in column D, is this possible?

If the lookup list (1 = $50, 2 = $100, etc) isn't too long

Try in D1:
=IF(A1="","",IF(ISNA(MATCH(A1,{1;2;3},0)),"No match",
VLOOKUP(A1,{1,50;2,100;3,120},2,0)))
Copy D1 down as far as required

(I don't have a suggestion for your 2nd Q, sorry)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


0
demechanik (4694)
1/19/2006 6:40:55 AM
Hi!

> I need to make 1 = $50 and 2 = $100 and 3 = $120 etc
> I need to do this by entering 1, 2 or 3 in column A and the value
> automatically appearing in column D, is this possible?

=LOOKUP(A1,{0,"";1,50;2,100;3,120})

> Also I would like to hide a column and only show cells from that column in
> the row that I apply value to in another cell of the same row. Essentially
> hiding the whole column and only showing value in a row when the row is in
> use.

You can do that using conditional formatting but you'd need to provide more 
specific details.

Biff

"Kylie Rose" <KylieRose@discussions.microsoft.com> wrote in message 
news:06A04B91-70C9-4830-B230-44033869AC7E@microsoft.com...
> Please help me....
> Example:
> I need to make 1 = $50 and 2 = $100 and 3 = $120 etc
> I need to do this by entering 1, 2 or 3 in column A and the value
> automatically appearing in column D, is this possible?
>
> Also I would like to hide a column and only show cells from that column in
> the row that I apply value to in another cell of the same row. Essentially
> hiding the whole column and only showing value in a row when the row is in
> use.
>
> Please help me, I want to create something for a job interview and can't
> work it out!
>
> Thank you so much whoever can answer this. 


0
biffinpitt (3172)
1/19/2006 6:47:23 AM
> =LOOKUP(A1,{0,"";1,50;2,100;3,120})

If col A contains numbers higher than 3, I'm not sure if
the result returned: 120 would be a mite misleading here ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


0
demechanik (4694)
1/19/2006 7:25:46 AM
Thank you very much!!!!!
You're excellent!
Cheers
Kylie

"Biff" wrote:

> Hi!
> 
> > I need to make 1 = $50 and 2 = $100 and 3 = $120 etc
> > I need to do this by entering 1, 2 or 3 in column A and the value
> > automatically appearing in column D, is this possible?
> 
> =LOOKUP(A1,{0,"";1,50;2,100;3,120})
> 
> > Also I would like to hide a column and only show cells from that column in
> > the row that I apply value to in another cell of the same row. Essentially
> > hiding the whole column and only showing value in a row when the row is in
> > use.
> 
> You can do that using conditional formatting but you'd need to provide more 
> specific details.
> 
> Biff
> 
> "Kylie Rose" <KylieRose@discussions.microsoft.com> wrote in message 
> news:06A04B91-70C9-4830-B230-44033869AC7E@microsoft.com...
> > Please help me....
> > Example:
> > I need to make 1 = $50 and 2 = $100 and 3 = $120 etc
> > I need to do this by entering 1, 2 or 3 in column A and the value
> > automatically appearing in column D, is this possible?
> >
> > Also I would like to hide a column and only show cells from that column in
> > the row that I apply value to in another cell of the same row. Essentially
> > hiding the whole column and only showing value in a row when the row is in
> > use.
> >
> > Please help me, I want to create something for a job interview and can't
> > work it out!
> >
> > Thank you so much whoever can answer this. 
> 
> 
> 
0
KylieRose (3)
1/19/2006 7:38:04 AM
Thank you heaps!!
Help much appreciated!
Cheers
Kylie

"Max" wrote:

> "Kylie Rose" wrote:
> > .. I need to make 1 = $50 and 2 = $100 and 3 = $120 etc
> > I need to do this by entering 1, 2 or 3 in column A and the value
> > automatically appearing in column D, is this possible?
> 
> If the lookup list (1 = $50, 2 = $100, etc) isn't too long
> 
> Try in D1:
> =IF(A1="","",IF(ISNA(MATCH(A1,{1;2;3},0)),"No match",
> VLOOKUP(A1,{1,50;2,100;3,120},2,0)))
> Copy D1 down as far as required
> 
> (I don't have a suggestion for your 2nd Q, sorry)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> 
> 
> 
0
KylieRose (3)
1/19/2006 7:38:04 AM
>I need to do this by entering 1, 2 or 3 in column A

I don't see anything that mentions values higher than 3.

Biff

"Max" <demechanik@yahoo.com> wrote in message 
news:eyV5PnMHGHA.916@TK2MSFTNGP10.phx.gbl...
>> =LOOKUP(A1,{0,"";1,50;2,100;3,120})
>
> If col A contains numbers higher than 3, I'm not sure if
> the result returned: 120 would be a mite misleading here ?
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
>
> 


0
biffinpitt (3172)
1/19/2006 7:42:02 AM
"Biff" wrote:
> >I need to do this by entering 1, 2 or 3 in column A
>
> I don't see anything that mentions values higher than 3.

Agreed.  But I was hinting at the possibility
of a "wrong" input of a number higher than
what is mentioned in the OP
(the built-in "error trap" part of it)

Anyway, the OP seems happy with both options <g>
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


0
demechanik (4694)
1/19/2006 10:02:57 AM
You're welcome !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kylie Rose" <KylieRose@discussions.microsoft.com> wrote in message
news:3DA563A1-8D19-4465-B483-9D3D5454264E@microsoft.com...
> Thank you heaps!!
> Help much appreciated!
> Cheers
> Kylie


0
demechanik (4694)
1/19/2006 10:03:56 AM
Reply:

Similar Artilces:

Suppressing categories with 0 values
Hi all I'm trying to build a dynamic chart for sales data. The output file contains two columns, A and B. Column A contains categories, i.e. Apples, Oranges, Pears, etc.,. and B contains sales data, i.e. 5, 0, 10, etc.,. respectively. The ouput data comes out monthly. Some months will contain sales from all categories, some ten, some fifty. I want the chart to only graph categories with non-zero values, i.e. if there are 100 categories and only 30 with sales numbers there is no point in showing the other 70 categories with zero values. I can format column B with zero values to show ...

How do I modify the value of a checkbox in a word document using C
Hi all, I am a bit of a newbie at C# and am trying to set up a document that I will be modifying based on values from a database. I am using a word document, because it is the "official" reporting form required by my company. I can open the document, and write to bookmarks that I am setting in each table field, but I need to know how to manipulate a checkbox. Can anyone help me? I have edited this post because I have added some other code. The document file I am using is a document that has everything inside a table. I have bookmarked all the text locations, and...

Worksheet Changes
Hi All, HYCH Have a worksheet that has data in the range A5:I105, would like cell a2 to show a date that any of this data has changed, was thinking of using a worksheet change_Event to manage this but not sure of how to go about setting this up, have used the simple change event for a single selection i.e A1 or B4 but not with a range area. Any help would be Great Steve A bit more explanation along with layout and what desired output looks like. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Steve" <instructorf@hotmail.com> wrote in mes...

Find a value in list 2 that is not in list 1
I have text values in column A and also in column B, most of which match. I would like to take the values in column B that are not in column A and put those values only in column C. Any help would be greatly appreciated. Maxxwell, Put this formula in column C, and copy down. =IF(ISERROR(MATCH(B2,$A$2:$A$20,0)),B2,"") To get them contiguous, use Autofilter, filter on nonblanks in column C, and copy/paste to somewhere. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "maxxwell2" <maxxwell2@discussions.microsof...

Cell comments
I have added cell comments to my worksheet, but they have always bee static, meaning if you put your cursor on that cell it always display the same text. Is there a way to create some sort of comment tha would change depending on the information in the cell? When the curso passes over the cell, the code would go pull in data from a MS Acces table using the value in that cell as criteria -- LA ----------------------------------------------------------------------- LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=965 View this thread: http://www.excelforum.co...

RE: MSCRMDeletionService -- change frequency...
i am trying to modify the frequency for the MSCRMDeletionService...there is a registry value in the HKLM\Software\Microsoft\MSCRM\ key named DSRunInterval. one of our servers has this value as a REG_SZ (string) value while another server has it as a DWORD (number) value ; does anyone have an idea what format this value should be? and also is it based on seconds? thanks! sunish ...

changing the delimiter while saving as txt file
Dear Experts, is there a way to change the delimiter to # instead of tab characters while saving the excel file to .txt file. Thanks in advance Hi see: http://www.cpearson.com/excel/imptext.htm -- Regards Frank Kabel Frankfurt, Germany "raj" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:845d01c48520$92fc1c20$a401280a@phx.gbl... > Dear Experts, > > is there a way to change the delimiter to # instead of tab > characters while saving the excel file to .txt file. > > Thanks in advance And some more links... Maybe you can steal some cod...

Customise doesn't keep changes.
Hi!! I have Money 2005... I would like to customise some report in order to avoid some categories in reports. I did it for "Spending by Categories" report.. I ommited some and it worked great, howevver, when I return to home the report still show the categories I ommited and when I go to the report waht I customised is gone. Is there a bug in this? Can I create new report from nothing at all? or from a template? In microsoft.public.money, Marcell wrote: >Hi!! >I have Money 2005... I would like to customise some report in order to avoid >some categories in reports....

Purge data based on another Excel file???
I am attempting to purge data from a large list based on data in another document. I have the files I want to remaove in on file and the file I want to remove them from. There should be simple way to do this no? ...

eliminating blank cells
I have two sheets in a workbook. Say sheet January and Summary. Sheet January looks like this DATE SN Amount TAX OVD CBS 12.01.2007 483 3464 1559 701 1184 13.01.2007 484 3907 1758 791 -627 14.01.2007 485 241 108 49 -75 17.01.2007 9/A12 1623 730 329 104 19.01.2007 11/A2 329 839 292 88 Now I want to gather these information in sheet "Summary" eliminating the blank rows, so Summary looks like this: DATE SN Amount TAX OVD CBS 12.01.2007 483 3464 1559 701 1184 13.01.2007 484 3907 1758 791 -627 14.01.2007 485 241 108 49 -75 17.01.2007 9/A12 1623 730 329 10...

OWA Change password option
I've read through your various postings and articles, relating to OWA change password option under a front-end / back-end 2003 SP2 senario. Please can you clarify the following steps I need to follow: 1) Create a new Iisadmpwd virtual directory in IIS on the frontend server. 2) Run the OWAADMIN tool, and select enable password feature on both the front-end and back-end servers. Can you clarify if by using the OWAADMIN tool, that I won't have to manually edit the registry on both servers as the tool will take care of this for me? Can you also clarify, which services (if a...

Can't change default print resulotion in Publisher 2003
In Publisher 2003, I tried to change the print quality via the "Printer Properties" dialog box, but that didn't change the print resolution. I clicked "Advanced Print Settings..." in the print dialog box, but the only drop-down choice next to "Resolution" is "Default," which doesn't say what the default is but seems to be set at Super-fine. How do I cange the print resolution to Normal or Draft? Thank you. Don Burgess <DonBurgess@discussions.microsoft.com> was very recently heard to utter: > In Publisher 2003, I tried to change...

Changing Functional Currency #3
Hi There is a live company ABC. We created the new company XYZ then I restore the live company database ABC to the newly created company XYZ by following the KB article 871973. Then we delete the all the transaction in XYZ company because we need the same company setup and masters with that we added required additional master data. The problem we are facing is we need to change the functional currency in XYZ company. How can we do this? Any one can help us Regards Nizham I don't believe you can change the functional in XYZ since there were transactions. Howabout creating a 123 com...

Copying text value based on long (comlex) calculation
I am not sure how to even search for what I want to do, so please forgive me if my first post has been discussed and resolved ad naseum in the past... Here is my situation: - I have a workbook that contains multiple sheets. - Each sheet contains auto parts from different suppliers, sorted by part number. - Some suppliers have parts that overlap with each other and the various sheets are updated as pricing and availabilty of parts change. - I am using a combination of the VLOOKUP and MIN functions to determine which supplier has lowest cost based on a comparison of part numbers and price ac...

I want a formula to ignore text values in cell references
If text is input into some cells referenced by a formula I want the formula to ignore whatever text it finds and just calculate the result of the numbers in cells. How do i do this? Presently the formula displays #VALUE! where there is text in one or more of the cells referenced. Use ISNUMBER(): In place of A1 use =IF(ISNUMBER(A1),A1,"") -- Gary''s Student "Russellrupert" wrote: > If text is input into some cells referenced by a formula I want the formula > to ignore whatever text it finds and just calculate the result of the numbers > in cells...

Changed relationship; records no longer linked
I have a Corporates table holding details of company customers. I have a CorpContacts table holding details of our contact persons at those companies. Originally I had a relationship (one to many) between Corporates.CorpName and CorpContacts.CorpName. I had a Corporates form which contained a CorpContacts subform displaying the contact persons' details. All working fine. But if we updated the name of the company i.e. Corporates.CorpName, the link was lost and the contact persons were no longer displayed. So I introduced new fields in each table ("CorpID"), i.e. some...

Changing OLAP Pivot Table Connection Source
I have numerous spreadsheets that have pivot tables pointing to an OLAP Cube via a connection string. Is there an easy way (either automated or manual) to change the connection string to point to a new version of the OLAP Cube? I can edit the connection properties and navigate to the new .odc connection file, but get an error saying "The OLAP provider returned an unexpected number of dimensions." ...

in Excel: How do I change "selected cells" highlight color?
When I select cells in Excel the shading is so light (blue) that I can barely tell which cells are selected from those that are not. Can I change the background color of cells that are selected? Thanks. Assuming Excel 2007 this is a known problem and as yet no patch for it. I don't have Excel 2007 but I have read that playing with your Contrast can help some. Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 12:51:00 -0700, flameretired <flameretired@discussions.microsoft.com> wrote: >When I select cells in Excel the shading is so light (blue) that I can barely >tell whic...

How to make A1 the active cell in all visible worksheets ?
This is a multi-part message in MIME format. ------=_NextPart_000_0028_01CA8164.76257740 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ......so that when i activate the sheet A1 is the cell in the upper left = corner of my screen. Thanx ------=_NextPart_000_0028_01CA8164.76257740 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; ...

Conditional Formatting dependent on whether cell is populated.
I want to conditionally format cell G2 dependent upon whether there is a value in cell C2 AND G2 Therefore, if cell C2 has a value in it and cell G2 doesn't then cell G2 color = Amber if cell C2 has a value in it and cell G2 has a value then cell G2 color = Green Any helpful hints or useful suggestions? Thanks Tel Conditional Format of G2, condition 1: =AND(COUNTA(C2)=1,ISBLANK(G2)) format Amber condition 2: =COUNTA(C2,G2)=2 format Green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tel" wrote: &...

connecting cells with connector lines
Hello! Is it possible in Excel (2007) to draw a connector line (with or without an arrow ...) between two cells in a worksheet, so that the line-tips will follow their cells even when the latter are moved ? Thanks Michael On Feb 25, 7:00=A0am, Michaelprem123 <michaelprem...@gmail.com> wrote: > Hello! > > Is it possible in Excel (2007) to draw a connector line (with or > without an arrow ...) between two cells in a worksheet, so that the > line-tips will follow their cells even when the latter are moved ? > > Thanks > > Michael The line will automatically a...

Change Percentage DIV/0! Error
I'm trying to see the change between (A) 2002 unit sales and (B)200 unit sales, and am using the following formula: =(B-A)/A This gives me the difference negative or positive, however if A (200 sales) is zero, then I get the DIV/0! error, how do I fix this? Thank you -- Message posted from http://www.ExcelForum.com Hi, A couple of things you might want to consider. One is that your formula will not take into account what the change is if the prior month base amount is negative. To fix that use the =IF(base=0,"",(current-base)/ABS(base)) This should help >-----Or...

Summing items in a cell
How do you SUM items in a cell. I have a parts list with reference designators like U3-U6. I want the sum of the range of items, in this case it's 4, but can't figure out how to do this. Any help will be appreciated -- dpmoore ------------------------------------------------------------------------ dpmoore's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24920 View this thread: http://www.excelforum.com/showthread.php?threadid=384566 More info and then a formula using find can be used or a udf (macro for custom formula) -- Don Guillett SalesAid Soft...

2 different fonts in the same cell?
Hello! If I want (Times New Roman) "This is a red Wingdings 3 triangle ([change font] Wingdings 3: 123 [back to Times New Roman]").", I can do that in a cell. But if I refer to that cell (=A1) in B1, the Wingdings font doesn't come over and it winds up all TImes New Roman and looks like "This a red Wingdings 3 triangle ({)." How can I "=A1" for a cell that has two fonts in it and have the two fonts show up?? Thanks! VR/ Lost You can't have that in a FORMULA. You would have to change it to text first. -- Don Guillett Microsoft MVP Excel Sales...

How to tell Excel to insert cells and shift down from Access
Hi all, I have Access 2003. I am trying to tell Excel to insert cells and shift cells down from Access, with this line of code: oWksh.Cells.Insert Shift:=xlDown But Access does not like that line. It gave me run-time error 1004. I know if has to do with the part Shift:=xlDown Can you suggestion what I can do? Thank you in advance, Ben "Ben" <Ben@NoSpam.com> wrote in message news:eSZuzcDpKHA.1892@TK2MSFTNGP02.phx.gbl... > Hi all, > > I have Access 2003. I am trying to tell Excel to insert cells and shift > cells down from Access,...