+ Reply to Thread
Results 1 to 12 of 12

Activating one cell with a number if one of two cells has an entry/number in it.

  1. #1
    Registered User
    Join Date
    09-11-2013
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Activating one cell with a number if one of two cells has an entry/number in it.

    Hi

    I have just discovered this Forum and I am hoping somebody can help me out.

    I am trying to work out the following.

    I have three cells. One cell will offer me a number multiplied by 1.3333 - the result will come from the activation of two other cells.

    So if cell 2 has a number placed into it like 4 i would like a roundup of 6 to be in cell 1, if cell 2 has 0 in it i would like it to be inactive and draw the result from cell 3. The same if cell 3 has a number, it will activate cell 1. If both cells 2 and 3 have 0 in then neither are activated.

    Does that make sense?

    I have tried putting two roundup formulas in side by side with an IF or an AND in but my calculations keeps showing an error. But this was just a guess.

    Hope someone can help

    Thanks

    Paul

  2. #2
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Activating one cell with a number if one of two cells has an entry/number in it.

    welcome to the forum..
    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data. and put some manual expected result also

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  3. #3
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Activating one cell with a number if one of two cells has an entry/number in it.

    Try this formula in cell A1 - it will look at values in cells B1 and C1:

    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Registered User
    Join Date
    09-11-2013
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Activating one cell with a number if one of two cells has an entry/number in it.

    Hi Olly

    That is great, it worked a treat - thank you, if only I had asked one and a half hours ago! If i wanted to remove the Roundup is it okay to just delete that or would the whole formula need revising?

    Thanks once again.

    Paul

  5. #5
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Activating one cell with a number if one of two cells has an entry/number in it.

    Without roundup:

    Please Login or Register  to view this content.
    You're welcome.

  6. #6
    Registered User
    Join Date
    09-11-2013
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Activating one cell with a number if one of two cells has an entry/number in it.

    Hi Olly

    You have opened a can of worms for me here.

    This is my final question of the day. Using the above formula, without roundup, can a variable be inserted so that cell A1 chooses the lower of the two figures produced in cell B1 and C1?

    i.e.

    A1 B1 C1
    5 5 6

    or

    A1 B1 C1
    5 7 5

    Can it be shown that if B1 is the lowest number that the figure is TRUE as well? Or is that beyond the world of excel?

    i.e.

    A1 B1 C1 D1
    TRUE 5 5 6

    and such the result if D1 were lower FALSE?





    Thanks

    Paul

  7. #7
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Activating one cell with a number if one of two cells has an entry/number in it.

    It's easy to choose the minimum value:

    Please Login or Register  to view this content.


    For your second requirement, in cell A1:

    Please Login or Register  to view this content.

    Does this answer your questions, or did you need to combine these with the "ignore if the same" logic, as posted earlier?

  8. #8
    Registered User
    Join Date
    09-11-2013
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Activating one cell with a number if one of two cells has an entry/number in it.

    I was trying to combine the two if possible. But that has come in very handy. I think I need to buy an Excel Bible.

  9. #9
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Activating one cell with a number if one of two cells has an entry/number in it.

    It's straightforward, but I am losing track of your requirement, now!

    To make sure I deliver what you are expecting, can you upload a workbook with several rows, showing examples of the potential combinations of input values, and your required outputs, please?

  10. #10
    Registered User
    Join Date
    09-11-2013
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Activating one cell with a number if one of two cells has an entry/number in it.

    Hi

    I think this explains it. I am trying to get a sheet where it calculates using the lowest figure instead of me checking everything 100 times.

    Thanks

    Paul

    excelhelp.xlsx

  11. #11
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Activating one cell with a number if one of two cells has an entry/number in it.

    Got it. See attached workbook - I've updated the formulae in cells F22 and G22. I think this now does what you want!

    UseMinPrice.xlsx

  12. #12
    Registered User
    Join Date
    09-11-2013
    Location
    Stoke on Trent, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Activating one cell with a number if one of two cells has an entry/number in it.

    Fantastic thank you very much!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. count the number of blank cells from the last entry
    By Gerz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-12-2013, 02:18 AM
  2. Automatically Add a Number to Cell Entry
    By BazzaBoy in forum Excel General
    Replies: 6
    Last Post: 07-23-2010, 01:57 AM
  3. Find the row number of the first cell entry in a column
    By catmandu57 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2008, 03:16 PM
  4. Replies: 4
    Last Post: 01-02-2007, 07:50 AM
  5. [SOLVED] Want a number of cells to be auto completed based on entry in one.
    By matt ashley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1