-
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...
-
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
-
1 Attachment(s)
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.
Attachment 153
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
-
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
------------------------
-
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
