+ Reply to Thread
Results 1 to 10 of 10

Drawing a value from a cell

  1. #1
    Registered User
    Join Date
    06-08-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    25

    Drawing a value from a cell

    I have a spreadsheet I am using for structural design. In it I evaluate values for seismic loading and wind lateral loading on shear walls. In column D I have seismic loads (lb/ft), in column E I have wind loads (lb/ft). In column H I use a formula to calculate the load on a wall based on the Max of columns D and E. My formula is:

    =IF(MAX(D11:E11)=D11,D11*C11*$B$9&" Seismic", E11*C11*$B$9&" Wind")

    As you can see, I figured out a way to add text to the cell so when it does the calculation if column D results in a higher load then it adds the text "Seismic" to the cell and if column E results in the higher load it adds the text "Wind".

    My issue now is that the actual value in this cell which is derived from the formula gets used by other cells in order to calculate other things. Upon adding that text now my other cells give the #VALUE! error. So my question is, is there a way in the subsequent cells to have a formula that draws the value from column H? Is there a way, for example, for an equation to "remove" the word and only see the value?

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Drawing a value from a cell

    Use just this formula without the text

    =IF(MAX(D11:E11)=D11,D11*C11*$B$9&" Seismic", E11*C11*$B$9

    Then format the Cell (Ctrl+1) as #" Wind"
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: Drawing a value from a cell

    you get better help, if you add a small excel file, without confidential information.

    Please also add the expected result in your file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Drawing a value from a cell

    You can also have the word "Seismic" and "Wind" calculate in another column to the right of the number. That way the number stays as a number that can be used in other calculations and you can still tell visually which was larger.
    Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.

    <---Click * Add Reputation for all helpful comments. It's like giving a smile.
    Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.

  5. #5
    Registered User
    Join Date
    06-08-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Drawing a value from a cell

    Thank you Ace. That gets me halfway there.

    This is helpful, but it only works if my Wind (column E) gives me the higher value. If column D gives me the higher value it's still seeing that text and screwing me up.

    If all else fails I'll use another column as nigel suggests.

  6. #6
    Registered User
    Join Date
    06-08-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Drawing a value from a cell

    I have attached an excerpt of the file if that helps.

    Please note, that the formula in the related cells has changed from what I originally posted, though it provides me with roughly the same result. It's just more correct from a technical (engineering) standpoint. But the formula still gives me a max value and appends the aforementioned text depending on the value.
    Attached Files Attached Files

  7. #7
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Drawing a value from a cell

    Sorry..my bad. Didn't read the request fully.

    You would need an additional step of Conditional formatting (CF) further to the original cell format. In CF, use this rule

    =MAX(D11:E11)=D11

    Format as #" Seismic"

    See attached as an example
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-08-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Drawing a value from a cell

    Thanks Ace. I might go that route. Is there a way to copy that down the column though? If I format paint it applies it to the whole column, but the cells used in the formula condition remain the same. For example it starts in row 11, but if I copy down to say row 25 the formatting in row 25 is based on the cells in row 11. I want the cells to match the adjacent ones.
    Last edited by Jeffjr02; 06-15-2015 at 01:17 PM.

  9. #9
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Drawing a value from a cell

    Use the $ sign for absolute or relative referencing in the CF formulas as required. In your case, you would need relative referencing

    Also, set up the CF whilst selecting all the cells initially, rather than copying and pasting formats or using the format painter.

    See your updated Test file
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-08-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Drawing a value from a cell

    Ace, Can you provide some further info? I can't mimic what you did with the CF. So if I have D11 to 25 and E11 to 25 and I want to use the formatting in H. Are you saying I select H11:H25 go to the CF tab and do "use a formula"? I don't know if I am doing something in the wrong order or what. I cannot duplicate your CF. If I actually look at your CF Rules it looks wrong, but it works perfectly.

+ 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. Replies: 0
    Last Post: 05-25-2014, 02:33 PM
  2. cell value dependent drawing in excel
    By P Kundu in forum Excel General
    Replies: 0
    Last Post: 11-25-2013, 02:23 AM
  3. Drawing an ellipse from cell data
    By Stoolie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2011, 04:03 PM
  4. drawing autoshapes according to cell value
    By Mikaeli in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-12-2006, 02:44 AM
  5. [SOLVED] how to replace drawing with text in that cell
    By Hshah in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2005, 10:05 AM

Tags for this Thread

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