format of cells

[willemeulen]

I have a custum format for the cell in order to show an empty cell whe
the answer is 0.

My custum format is as follows:

0;-0;""

The aswer is collected by a hlookup funcion using row and referenc
cell

In one particular case the table shows Invalid C as the answer but m
lookup returns #value#

Suggestions?:Bg

 

[NBVC]

Can you elaborate on this. What does it mean, Invalid C?

In one particular case the table shows Invalid C as the answer but m
lookup returns #value

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[willemeulen]

Can you elaborate on this. What does it mean, Invalid C?

It just a result from an IF function in the formula. Normaly th
formula will return an answer but when a value in worksheet reffered t
as C exceeds a certain value it will show the user invallid C

Check my formula below:Bgr

=IF('Bending Schedule'!N33>=4*INDEX('Table''s'!$A$4:$K$6,MATCH('Bendin
Schedule'!E33,'Table''s'!$A$4:$A$6,0),MATCH('Bendin
Schedule'!F33,'Table''s'!$A$4:$K$4,0)),ROUND(('Bendin
Schedule'!L33+'Bending Schedule'!M33+0.57*'Bendin
Schedule'!N33+'Bendin
Schedule'!O33-0.5*INDEX('Table''s'!$A$4:$K$6,MATCH('Bendin
Schedule'!E33,'Table''s'!$A$4:$A$6,0),MATCH('Bendin
Schedule'!F33,'Table''s'!$A$4:$K$4,0))-2.57*'Bendin
Schedule'!F33+IF('Bending Schedule'!N33>=400,2*'Bendin
Schedule'!F33,0)),0),"Invalid C")

Yes its a hell of a formula:Bgr but it works fine, as you can see th
if function return invallid C when .....bla bla bla

My problem is when the result is indeed invallid c it does not show o
my front page, the lookup function returns #value#

My best guess is I have to change the cell format, my current cel
format is

0;-0;""

This format will show empty cells when the result is 0, in other word
when the particular row is not in use

 

[NBVC]

Try:


Code
-------------------
=LOOKUP(REPT("Z",255),CHOOSE({1,2},"Invalid C",IF('Bending Schedule'!N33>=4*INDEX('Table''s'!$A$4:$K$6,MATCH('Bending Schedule'!E33,'Table''s'!$A$4:$A$6,0),MATCH('Bending Schedule'!F33,'Table''s'!$A$4:$K$4,0)),ROUND(('Bending Schedule'!L33+'Bending Schedule'!M33+0.57*'Bending Schedule'!N33+'Bending Schedule'!O33-0.5*INDEX('Table''s'!$A$4:$K$6,MATCH('Bending Schedule'!E33,'Table''s'!$A$4:$A$6,0),MATCH('Bending Schedule'!F33,'Table''s'!$A$4:$K$4,0))-2.57*'Bending Schedule'!F33+IF('Bending Schedule'!N33>=400,2*'Bending Schedule'!F33,0)),0),"Invalid C"))
-------------------

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[willemeulen]

Funny cgrin

I know my formula looks rediculous but it actually works, its mainl
the matching within the formula which makes it so long. And no I canno
make it shorter.

If you would like to have a bending schedule to south african standard
(read spreadsheet to calculate cut length, quantities and kg fo
reinforcing steel) give me a PM and I will forward you the sheet :Bgr
it's been a very interesting process to make the actual thing. With har
work and a lot of communication with the code cage I learned a lot.

Now I'm just ironing out the little nitty gritty stuff I received a
little bugs from users in the office.

By the way the lookup funcion I use looks as follows

=CEILING(HLOOKUP(K16,Cutlength!$A$2:$AF$332,ROW(),FALSE),10)

)

 

[NBVC]

Not sure by your post, if you are resolved or not?

Basically, If a Lookup function doesn't find a match it returns #N/A
not #VALUE!.. so if you are getting the latter, then you have a #VALUE!
error within the lookup data already and that needs to be fixed... or
you are trying to manipulate text as numbers... (ie. perpahs multiplying
a text entry with a number entry?).


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)

 

[willemeulen]

When C is valid and formula returns a number (cutlength sheet) th
lookup function (bending schedule sheet) works fine, it's just when th
answer is "invalid C" (cutlength sheet shows invalid C) the looku
returns #value!

Even if I change cell format (bendeng schedule sheet) to number, text
general the cell remains #value!

It would be a pity the user would have to find out himself the "C" i
invalid whyle the answer is already there

 

[NBVC]

Then perhaps?


Code
-------------------
=IF(INDEX('Table''s'!$A$4:$K$6,MATCH('Bending Schedule'!E33,'Table''s'!$A$4:$A$6,0))="Invalid C","Invalid C",IF('Bending Schedule'!N33>=4*INDEX('Table''s'!$A$4:$K$6,MATCH('Bending Schedule'!E33,'Table''s'!$A$4:$A$6,0),MATCH('Bending Schedule'!F33,'Table''s'!$A$4:$K$4,0)),ROUND(('Bending Schedule'!L33+'Bending Schedule'!M33+0.57*'Bending Schedule'!N33+'Bending Schedule'!O33-0.5*INDEX('Table''s'!$A$4:$K$6,MATCH('Bending Schedule'!E33,'Table''s'!$A$4:$A$6,0),MATCH('Bending Schedule'!F33,'Table''s'!$A$4:$K$4,0))-2.57*'Bending Schedule'!F33+IF('Bending Schedule'!N33>=400,2*'Bending Schedule'!F33,0)),0))
-------------------


If this doesn't work, post a workbook..

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[pshepard]

Hi Willemeulen,

The following formula works for me, producing "Invalid C" even when the
custom format is 0;-0;""

=IF('Bending Schedule'!N33>=4*INDEX(Tables!$A$4:$K$6,MATCH('Bending
Schedule'!E33,Tables!$A$4:$A$6,0),MATCH('Bending
Schedule'!F33,Tables!$A$4:$K$4,0)),ROUND(('Bending Schedule'!L33+'Bending
Schedule'!M33+0.57*'Bending Schedule'!N33+'Bending
Schedule'!O33-0.5*INDEX(Tables!$A$4:$K$6,MATCH('Bending
Schedule'!E33,Tables!$A$4:$A$6,0),MATCH('Bending
Schedule'!F33,Tables!$A$4:$K$4,0))-2.57*'Bending Schedule'!F33+IF('Bending
Schedule'!N33>=400,2*'Bending Schedule'!F33,0)),0),"Invalid C")

It seems that the names of the worksheets may have had invisible characters
- so try rekeying in the worksheet names. I noticed that in your post
'Table''s' had two consecutive single hyphens - which will cause problems.
However I also found that there was something odd about the worksheet name
'Bending Schedule' as well.

 

[willemeulen]

Peggy,

I changed names but to no effect. Just to be clear I do not have
problem with producing "invalid C", the formula works fine. It's jus
the lookup function which refuses to return this result and show
#value! instead.

 

[NBVC]

You are a member of the CodeCage forum where you can post workbooks.
why not post one instead of going in circles..

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[willemeulen]

Herby the workbook.

Sheet "bending schedule"

shaded cells will be completed by user (light yellow and blue), th
blue cells will only appear once you filled in a shape code

The red cell shows i used shape code 85, I made value C too low (o
purpose)
The cutlength sheet shows all possible results for every shape cod
available
Now the result for shape code 85 is "invallid C"
But the Hlookup function on bending schedule sheet doesnt want t
return this value but returns #value! instead

The answer you send me might work for this code in particular but won
work if my shape codes change.



+-------------------------------------------------------------------
|Filename: Copy of Master Bending Schedule 3.0.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=261
+-------------------------------------------------------------------

 

[NBVC]

This is because you are using the CEILING function... which requires a
numeric entry.. and "invalid C" is text... so, as I previously
mentioned, you get the #VALUE error when trying to manipulate Text in a
Numeric function.

So try:


Code:
--------------------
=IF(ISNUMBER(HLOOKUP(K4,Cutlength!$A$3:$AF$333,ROW()-2,FALSE)),CEILING(HLOOKUP(K4,Cutlength!$A$3:$AF$333,ROW()-2,FALSE),10),HLOOKUP(K4,Cutlength!$A$3:$AF$333,ROW()-2,FALSE))
--------------------


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)