SOLVED

Formula writing

Copper Contributor

I need help I am new to excel and I am trying to create a spread sheet that total the amount of drywall sheets need for a job. I already created a vlookup list that is associated with each sheet of drywall in cell A1.     which in another cell it automatically pops up the sqft of that particular sheet of drywall. which we will say it is in cell d1 now i have the total amount of sqft for the job in c1. Is there anyway I can add to the c1 formula in that same cell that would give me the total amount of boards needed for the job without adding another cell.  thanks Fred 

6 Replies
I don't understand your setup but lets take:
A1:B10 is table of dry wall sheet name and corresponding Sq Ft
C1 = SQFT for the job
D1 = Name of dry wall sheet to be used (should match a name in the table in col A and best if you use Data Validation on that cell which will create a drop down box to select from)
E1 = C1 / VLOOKUP(D1,A1:B10,2,FALSE) * 1.2
So E1 will show the total area divided by the area of an individual sheet and I multiplied by 1.2 to add a 20% overage factor that I find is typical for drywall and flooring calculations but feel free to eliminate or change accordingly.
Goodmorning mtarler,
I will write out exactly what I have. Sheet 1
A B C D E
39 Type and size of drywall Length of Drywall Height of drywall SQFT of Area Board Quantity
40 4x8x1/2 10ft 10ft 100 sqft 32 sqft
41

A = Sheet2!A21:B22 B = Length of the wall C = height of wall D = B40*C40
E = VLOOKUP(A21,Sheet2!A21:B22,2,False) Which actually is the sqft of each board. What I would actually like it to be is when I select what board it is that it gives me the actual amount of boards needed for the job I do not want to have to make another cell just for that. So is there anyway I can add to the vlookup formula which associates the the board selection 4x8x1/2= 32 to take that number and divide the 32 into D=100sqft and get the board quantity in the E cell? Or do I have to add another cell and put the formula in D40/E40? I hope this makes sense.
Sheet 2 Table


A B C
19 Column 1 Column 2 Column 3
20 Size of Drywall Sqft of Board Price
21 4x8x1/2 32 $12.82
22 4x12x1/2 48 $19.24

best response confirmed by VI_Migration (Silver Contributor)
Solution
Yes, just add it to the formula so the if it row 40 as in the example above just add the D40/
E = D40 / VLOOKUP(A21,Sheet2!A21:B22,2,False)
Unfortunately all the formatting was lost in your message so I'm confused about your VLOOKUP but assuming that it is returning the sqft of the selected board then this should work.
mtarler thank you very much that is what i needed it works perfectly. That was so easy Im shaking my head right now thank you again Fred_Y1972

According to me vlookup is an excellent tool in excel. It is very much useful when you have a big list of data and you want to pull the matching data with the values in one column

In simple terms

“VLOOKUP IS THE MAGNET TO PULL THE NEEDLE FROM THE HAYSTACK “

where the needle is the required data and the haystack is the master list of data.

An example, assume that you have a database of the list of employees working in the office ( say 10000 people ), and you have a list of 100 members who you want their Date of Birth and all the data is available in the master list.

Will you search for all 100 members one by one from the master list?

That's where vlookup kicks in if we insert the right formula the process will be done in a matter of minutes.

The formula goes like

{ =VLOOKUP( lookup value, array of content, column index number, condition) }

  • Lookup value - if the name of the employee whose DOB is to be searched, in our case A2.
  • Array of content - Is the master list where all the data is available.
  • Column index - The number of the column where the data will be available ( i.e. the DOB column is available at the 2nd position from the EMP Name column. refer image 1 )
  • Condition - 0 (FALSE) in most of the cases which match the exact values.

We can simply hold the cross-hair and pull for the rest of the rows required.

I personally use vlookup in almost daily to extract information from a pile of master data. It saves a ton of time by skipping the conventional method.

Best,
Zack Jerry
Essay Writer

Zach, I love that analogy of pulling the needle from the haystack. You are very correct and use of VLOOKUP in traditional excel. I don't know if you have Excel 365 but newer functions are now available that you should look into including XLOOKUP and FILTER. In your example above pulling 100 records would be ideal for FILTER(). XLOOKUP not only gives you more flexibility, built-in not found output, and more controls on the searching but is also (at least in most if not all cases) more efficient too.
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution
Yes, just add it to the formula so the if it row 40 as in the example above just add the D40/
E = D40 / VLOOKUP(A21,Sheet2!A21:B22,2,False)
Unfortunately all the formatting was lost in your message so I'm confused about your VLOOKUP but assuming that it is returning the sqft of the selected board then this should work.

View solution in original post