Stumped... Need Help with formula...

[Mykro]

I'm having a problem getting this formula correct..

=IF(AND(B1="SF",B2:B100=N12),B1,IF(AND(C1="LF",C2:C100=N12),C1,))

The result returned is 0, It should return "LF"

Any help would be greatly appreciated...

Thanks...

Michael

 

[Mykro]

If you replace the current input form which the current output is created, the output will change accordingly.

Hello Aladin,

There are two #VALUE ! errors...

There are two worksheets... SUMMARY! and WORKUP!
SUMMARY is created by default from Autocad... WORKUP is where I figure my pricing..

Here is the SUMMARY worksheet ...
.

Excel 2013/2016
	A	B	C	D
1	Description	SF	LF	EA
2	6in A Curb	6439.7	201.15	1
3	4in Sidewalk	100.59	91.782	1
4	Lgt Standard	0.401		14
5	4in Expansion Paper w_Chalk		1.931	1
6	4in Expansion Paper w_Chalk		1.888	1
7	4in Expansion Paper w_Chalk		1.974	1
8	4in Expansion Paper w_Chalk		2.18	1
9	4in Expansion Paper w_Chalk		2.046	1
10	4in Expansion Paper w_Chalk		1.943	1
Summary

Cell Formulas
Range	Formula
A1	Description
A2	6in A Curb
A3	4in Sidewalk
A4	Lgt Standard
A5	4in Expansion Paper w_Chalk
A6	4in Expansion Paper w_Chalk
A7	4in Expansion Paper w_Chalk
A8	4in Expansion Paper w_Chalk
A9	4in Expansion Paper w_Chalk
A10	4in Expansion Paper w_Chalk
B1	SF
B2	6439.682
B3	100.585
B4	0.401
C1	LF
C2	201.151
C3	91.782
C5	1.931
C6	1.888
C7	1.974
C8	2.18
C9	2.046
C10	1.943
D1	EA
D2	1
D3	1
D4	14
D5	1
D6	1
D7	1
D8	1
D9	1
D10	1

.

Here is the WORKUP worksheet...
.

Excel 2013/2016
	A	B	C	D
1	No.	Description	Qty	Units
2	1	6in A Curb	#VALUE!	#VALUE!
3	2	4in Sidewalk
4	3	Lgt Standard
5	4	4in Expansion Paper w_Chalk
Workup

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(INDEX(Summary!B2:D125,0,MATCH($D2,Summary!B1:D1,0)),--(SUBSTITUTE(Summary!$A$2:$A$125,CHAR(160),"")=SUBSTITUTE($B2,CHAR(160),"")))
D2	=LOOKUP(9.99999999999999E+307,INDEX(Summary!B2:D125,MATCH(SUBSTITUTE($B2,CHAR(160),""),SUBSTITUTE(Summary!A2:A125,CHAR(160),""),0),0),Summary!$B$1:$D$1)

Named Ranges
Name	Refers To	Cells
Summary	=Summary!$A$1:$D$10

.
.
Here are the formulas ...
.
" =SUMPRODUCT(INDEX(Summary!B2:D125,0,MATCH($D2,Summary!B1:D1,0)),--(SUBSTITUTE(Summary!$A$2:$A$125,CHAR(160),"")=SUBSTITUTE($B2,CHAR(160),""))) "

"=LOOKUP(9.99999999999999E+307,INDEX(Summary!B2:D125,MATCH(SUBSTITUTE($B2,CHAR(160),""),SUBSTITUTE(Summary!A2:A125,CHAR(160),""),0),0),Summary!$B$1:$D$1) "

Michael

 

[Aladin Akyurek]

Try not to quote long posts, also avoid posting formula instances.

Earlier input gives the impression that a description is either just SF or just LF or just EA. That seems to be false conclusion, right?

 

[Mykro]

Try not to quote long posts, also avoid posting formula instances.

Earlier input gives the impression that a description is either just SF or just LF or just EA. That seems to be false conclusion, right?

Aladin,

Sorry about the long post.. HTML Maker posted more than I thought.. I must have made the wrong option...

Yes.. False...
There is only the three criteria.. SF = Square Feet, LF = Lineal Feet, EA = Each. To be priced by: Area = SF, Length = LF, or by the Count = EA.

Attached on the top section is the data straight from Autocad.. Below is how it needs to look,the way I have it formatted... Also..When the file is created by Autocad it brings everything in with an apostrophe before it including numbers.. ex 'Hello, 'Goodbye, '12, '23435 etc.. Numbers are seen as text.. You can't create a template for the data either.. Autocad creates a new excel file each time data is extracted...

Excel 2013/2016
	A	B	C	D
1	Layer	Area	Length	Count
2	6in A Curb	6439.682	201.151	1
3	4in Sidewalk	100.585	91.782	1
4	Lgt Standard	0.401		14
5	4in Expansion Paper w_Chalk		1.931	1
6	4in Expansion Paper w_Chalk		1.888	1
7	4in Expansion Paper w_Chalk		1.974	1
8	4in Expansion Paper w_Chalk		2.180	1
9	4in Expansion Paper w_Chalk		2.046	1
10	4in Expansion Paper w_Chalk		1.943	1
11
12
13	Description	SF	LF	EA
14	6in A Curb		201.151
15	4in Sidewalk	100.585
16	Lgt Standard			14
17	4in Expansion Paper w_Chalk		11.96
Summary

Cell Formulas
Range	Formula
A1	Layer
A2	6in A Curb
A3	4in Sidewalk
A4	Lgt Standard
A5	4in Expansion Paper w_Chalk
A6	4in Expansion Paper w_Chalk
A7	4in Expansion Paper w_Chalk
A8	4in Expansion Paper w_Chalk
A9	4in Expansion Paper w_Chalk
A10	4in Expansion Paper w_Chalk
A13	Description
A14	6in A Curb
A15	4in Sidewalk
A16	Lgt Standard
A17	4in Expansion Paper w_Chalk
B1	Area
B2	6439.682
B3	100.585
B4	0.401
B13	SF
B15	100.585
C1	Length
C2	201.151
C3	91.782
C5	1.931
C6	1.888
C7	1.974
C8	2.180
C9	2.046
C10	1.943
C13	LF
C14	201.151
C17	11.96
D1	Count
D2	1
D3	1
D4	14
D5	1
D6	1
D7	1
D8	1
D9	1
D10	1
D13	EA
D16	14

 

[Aladin Akyurek]

What is input here (sample of data) and what is output/result? (Forget about AutoCAD that I don't know anything about.)

 

[Mykro]

Input/Sample Data is: A1:A10, Worksheet name: Summary!

Output/Desired Result is: A13:D17, Worksheet name: Workup!

 

[Aladin Akyurek]

Input/Sample Data is: A1:A10, Worksheet name: Summary!

Output/Desired Result is: A13:D17, Worksheet name: Workup!

How come 6in A Curb 201.151 under LF?

 

[Mykro]

In USA, Concrete Curb is estimated by length or lineal foot, LF. Concrete floors, Sidewalks, Concrete Paving in General is estimated by Area, Square feet or SF. Smaller concrete items are estimated by a single Count or lump sum price, which in my case by Each or EA.