Results 1 to 5 of 5

Thread: Split Cell and Lookup

  1. #1
    Junior Member
    Join Date
    Nov 2011
    Posts
    2
    Rep Power
    0

    Question Split Cell and Lookup

    Firstly, hi all! I'm new here and I hope you guys can help me and, in the future, I can help others...

    So here's the deal:
    I'm doing this table in which I put some information about rooms in a construction job, including: room areas, perimeter, height, wall areas, and windows/doors dimensions.

    To calculate wall area, I need to multiply the perimeter and the room height, and then subtract the windows/doors areas the room has. So I made this cell that lists the doors and windows the room has, in the following format: "W1,W1,W3,D1" (2 windows of type 1, one window of type 3 and a door of type 1).
    I need to know if it's possible to make Excel read this cell and automatically recognize the cells with those names, read their contents, and subtract them...

    To make it simpler to understand:

    Cell1: Perimeter
    Cell2: Height
    Cell3: Wall Area -> =(Cell1*Cell2)-?????
    Cell4: id's of the doors and windows -> W1,W1,W3,D1
    Cell5: Area of W1
    Cell6: Area of W3
    Cell7: Area of D1

    So I need that Cell4 displays the area of the wall - the area of doors and windows, but since those can change a lot in the project, I'd rather make it rational, and not need to update every formula when I change something in the project that afects many rooms...
    So if I change a W1 for a W2, I need it to automaticaly detects that W2 has another area and use it in the formula to get the actual area of walls.

    I'm thinking I should somehow be able to give Cell5 the "name" of "W1" and then make Cell4 "forward" the content of Cell5 to Cell3's formula.

    Is that possible? Am I being confusing? Here is a screenshot of the table I'm working at (please apologize since it's in portuguese, my native language, but if it helps: PĂ© direito = Height, J1 = W1 and P1 = D1 and so on)

    Image

    Btw sorry I didn't search for an answer first, but I just don't know what term to seach for...

  2. #2
    Junior Member
    Join Date
    May 2011
    Posts
    10
    Rep Power
    0
    bsig,

    Welcome to the ExcelFox forum.

    You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt that you would get any answer.

    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

    To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

    Have a great day,
    Stan

  3. #3
    Junior Member
    Join Date
    Nov 2011
    Posts
    2
    Rep Power
    0
    Thank you very much, Stanley!

    I now send the sample document as you said. On cell F10 I managed to get the result I wanted by manually writting the formula. This means I need the same result but with a different (smarter and easier) way to get it.
    I guess I don't need the "duplicate" with the results I need, then.

    quantitativos-lourenco.xls

    EDIT: Oh by the way... I need some formula or anything that looks like the following: "multiply height and perimeter, then subtract the value on the cell with the names (ids?) specified on cell4"

    Thank you again
    Last edited by bsiq; 11-21-2011 at 07:43 PM.

  4. #4
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    13
    Hi bisq,

    Paste

    Below Formula in ; F10

    Code:
    =(D10*E10)-VLOOKUP(A10,$H$3:$L$14,5)
    Just Copy Paste this formula anywhere in Column F and you will get the desired result.


    HTH
    ------------------------

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi,

    Better you need an UDF as there are cells having multiple 'OBS' to look.

    Code:
    Function PAREDES(ByRef LookupValue, ByRef LookupTable As Range, ByVal ColIdx As Long) As Double
    
        
        Dim TblAry      As String, x, i As Long
        
        If TypeOf LookupValue Is Range Then LookupValue = LookupValue.Value2
        
        TblAry = "'" & LookupTable.Parent.Name & "'!" & LookupTable.Address
        
        x = Split(LookupValue, ",")
        
        For i = 0 To UBound(x)
            PAREDES = PAREDES + Evaluate("vlookup(""" & Trim$(x(i)) & """," & TblAry & "," & ColIdx & ",0)")
        Next
        
    End Function
    How to use this UDF.

    Copy this code > Open the workbook > Hit Alt + F11 > Go to Insert > Module and paste the code. Now hit Alt + Q to close the VBE window.

    In F10:

    =(D10*E10)-PAREDES(A10,$H$3:$L$14,5)

    copy the formula in F23

    HTH
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 5
    Last Post: 05-28-2013, 03:00 AM
  2. Lookup From Cell Range By Matching Row and Column
    By paul_pearson in forum Excel Help
    Replies: 2
    Last Post: 03-07-2013, 02:02 PM
  3. Lookup lookup lookup just can't make it work
    By work2live in forum Excel Help
    Replies: 1
    Last Post: 12-08-2012, 11:48 PM
  4. Replies: 10
    Last Post: 08-19-2012, 12:53 PM
  5. Concatenate Multiple Lookup Values in Single Cell
    By Admin in forum Download Center
    Replies: 4
    Last Post: 04-06-2012, 09:07 PM

Tags for this Thread

Posting Permissions

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