Posts by zvladk

Re: Calculation based on three different criterias

Not quite. it calculates it yes... but it still pulls the weight from the weights page.. I need it to use the formula that is the last part of my formula...

=IF(ISNA(VLOOKUP(B2&C2,Weight!$C:$D,2,0)),"0",(A2)*(E2+F2/12)*(G2))

Otherwise it does exactly what my original formula does...

The problem is once it finds that the logical test is true being if it doesnt find whatever is in B2 and in C2 in the weight page it puts a zero.. i need it to put a zero there only if the columns are blank.. and if they are not blank to still do the rest of the formula (A2)*(E2+F2/12)*(G2)) with what ever weird numbers i plug in to G2...

Not sure if any of this makes any sence..

Hi everyone,
I have ran into some trouble with my formula to calculate the total weight of a certain item in my cost estimator worksheet.

I have attached a file and the formula that I'm having issues with is Column I (Weight)..

What I would like the formula to do is calculate the weight based on three different criterias..

1) If Columns B & C are blank I would like Column I to Display a "0". (which it does right now)

2) If Columns B & C are found in my Weight Tab then it needs to calculate the total weight in Column I. (which it also does right now)

3) If Columns B & C are not found in my Weights Tab and are not blank to still do the calculation as weight/ft and Net Cost are entered into the worksheet...


The reason for the 3rd criteria is its almost impossible to enter all types of sizes for some of the items
that we work with (very rare items that are abnormal sizes) so if something is entered that is rare i still need the calculation to happen.

Thank you for your help on this in advance..
Hope that this is possible,

Vlad

forum.ozgrid.com/index.php?attachment/38173/

Re: Populate multiple cells using 2 corresponding drop down lists

I figure it out.. Thank you Jeremy...

Now if you select HSS or Angle in the first column and then the size of that material in the 2nd column both the weight/foot and the price reflect teh correct numbers based on data on Material Tab.

Had to reconfigure my source doc a little, if interested on how this works I've attached a working file.

Thanks,
Vlad

forum.ozgrid.com/index.php?attachment/37368/

Re: Populate multiple cells using 2 corresponding drop down lists

What you did is great but:

1) the second column (Size) drop-down now contains not only the size but also the weight and the price options.

2) If in the same row i want to select HSS in the first column (Material) and the size associated to HSS in the second column (size) it gives me an error of #REF!

Could you please help me out on this one...

P.S.-----This will be a bid material take off sheet so any of the rows could be any of the materials. One time I could choose a row to be HSS the next it could be a W, and the next it could be an Angle... so on and so on...

Thanks.

Vlad

Hello
I'm trying to create a worksheet that populates cells corresponding to 2 drop down lists.


The list is a material list (it will contain many more rows that what are currently in there).

I'd like to be able to select the material type from a list in the 1st column and the size of the material in the second column (which I've created using data validation) but I'd like the next 2 cells to be populated with the information corresponding to that specific type of material and size.

I've named the data range and created the drop down lists but I don't know how to automatically populate the remaining 2 cells.

Also is there a quicker way to update the Data Validation formula in column 2 (Size) (=Indirect(A24))
than doing it manually for each cell. Like a Select the whole 2nd column and update all cells in it at once so the formula (=Indirect(A24)) will change the cell number to A25 in row 25 and A26 to row 26?

Sample worksheet attached.


Thanks,

Vlad
forum.ozgrid.com/index.php?attachment/37365/