Inserting an If Function

[Padthelad]

I'm having trouble inserting and extra if function at the beginning of my formula.

I have a 'Job sheet' which informs my workshop of what to produce based on what has been quoted (information for the job sheet is gathered from another sheet in the same worksheet).

Currently, I have the following table:-

	A	B	C	D	E	F	G	H
4	Building Type	Quantity	Size	Notes	Timber Skids	Galv Skids	Timber Base	Concrete Base
5	Badminton	1	3x3	Window			3x3
6	Windsor	1	4x4	no window

<tbody>
</tbody>

Cell B5 contains the formula =IFNA(INDEX(Quote!$A$13:$A$31,MATCH(A5,Quote!$B$13:$B$31,0)),0) which returns the value in the column that relates to 'Badminton' on the quote sheet in the same workbook.

Cell C5 contains the formula =IFERROR(VLOOKUP(+A5,Quote!$B$13:$C$31,2,0),0) which returns the value in the 'size' column on the quote sheet.

Cell G5 has the formula =IFERROR(VLOOKUP(+G4,Quote!$B$13:$C$31,2,0),0) which returns the value in the corresponding column next to 'Timber base' from my quote sheet.

The problem I am having is that there are other items on my job sheet that could potentially have a 'timber base'. At the moment, when 'timber base' is entered on the quote sheet, all the 'timber base' job sheet items are highlighted.

One workaround I have considered inserting a set of IF function at the beginning of my current formula to effectively say 'if there is a value in B5 AND C5 then do the the formula ( =IFERROR(VLOOKUP(+G4,Quote!$B$13:$C$31,2,0),0) )'

OR:-

Is there a specific formula that can 'return timber base value that is related to building type'

I'm having trouble getting my head round the logic of this.Any

Any help anyone can provide is very much appreciated.

Thanks,

Pad

 

[LiveToExcel]

Hi Pad,

Thanks for posting. If you know that the =IFERROR(VLOOKUP(+G4,Quote!$B$13:$C$31,2,0),0) ) portion that you are considering is correct, and you would just like to add the IF(AND()) at the beginning, the formula would look like this:

=IF(OR(ISBLANK(B5),ISBLANK(C5)),"",IFERROR(VLOOKUP(+G5,Quote!$B$13:$C$31,2,0),0))

LiveToExcel

 

[BiocideJ]

I am very unclear as to what is actually being stored in your Quote sheet.
From your VLOOKUPs it appears that column B *should* be the corresponding building type, however, the fact that you are looking up "Timber Base" also in column B has me confused. How is Timber base being stored in the Quote worksheet.

I think it would be helpful if you could provide the applicable headers and some data from that sheet in the manner you did for the Job sheet.

Thanks,

 

[Padthelad]

Hi Live to Excel,

Thanks for your reply. The formula seems to work, but not quite as intended.

This may frustrate you, but I have decided to use a different formula in cell G5 which is:-

=IFNA(INDEX(Quote!$A$13:$A$31,MATCH(G4,Quote!$B$13:$B$31,0)),0)

When I amend my formula to include yours:-

=IF(OR(ISBLANK(B5),ISBLANK(C5)),"",IFNA(INDEX(Quote!$A$13:$A$31,MATCH(G4,Quote!$B$13:$B$31,0)),0))

It still returns the value of having a 'timber base'. Which relates to another building, see below:-

Mobile Field Shelter	Quantity	Size	Notes	Timber Skids	Galvanised Skids	Timber Base	Concrete Base
Badminton	0	0	0	0	0	1	0
Windsor	1	4x4	left window	0	0	1	0

<tbody>
</tbody>

Quote is showing as 'Windsor' with a 'timber base'. Formula in cell G6 is:-

=IF(OR(ISBLANK(B6),ISBLANK(C6)),"",IFNA(INDEX(Quote!$A$13:$A$31,MATCH(G4,Quote!$B$13:$B$31,0)),0))

As you can see from the table above, the timber base is highlighted whether cells B6 or C6 have values in them.

BiocideJ,

Thanks for your reply also.

The timber base on the quote sheet is on a drop list from a validated field. It is selected in the same way as ‘building type’. I realise that logically it is hard to distinguish between which timber base relates to which building from the quote, which is why I am trying to make this workaround work on the job sheet.

Thanks for your help, further suggestions are gratefully received.

Thanks,

Pad

 

[BiocideJ]

So on a given row, when Timber base is selected in column B, how is it distinguished to be for a particular Building Type?

E.g. I select Timber Base in cell B7, how would someone know that the corresponding value next to it is the timber base size of the Windsor as opposed to the Badminton?

 

[Padthelad]

In short...they don't. It is very rare that more than one building type would be on a quote. Usually the timber base on the quote would relate to the only building on that same quote. I am trying to avoid a situation where 'timber base' is highlights all over the job sheet, when in fact it only relates to one building type.

I am redesigning the system the department already uses. My job spec was to keep it as close to the original way they are working as possible. I realize it is less than ideal.

Any ideas?

Thank you for your time on this.

Pad

 

[Padthelad]

is it not possible to insert an IF function at the beginning of my current formula to effectively say 'if there is a value in B5 AND C5 then do the the formula =IFNA(INDEX(Quote!$A$13:$A$31,MATCH(G4,Quote!$B$13:$B$31,0)),0)) ' if not....do nothing???

 

[BiocideJ]

is it not possible to insert an IF function at the beginning of my current formula to effectively say 'if there is a value in B5 AND C5 then do the the formula =IFNA(INDEX(Quote!$A$13:$A$31,MATCH(G4,Quote!$B$13:$B$31,0)),0)) ' if not....do nothing???

Yes. I guess I was misinterpreting what you were trying to do.
LivetoExcel's formula is mostly what you are looking for, except since the cells it is referring to have formulas in them, they will never be blank (even if they return no value).

I believe this to be what you are needing:

=IF(OR(B5="",C5=""),"",IFNA(INDEX(Quote!$A$13:$A$31,MATCH(G4,Quote!$B$13:$B$31,0)),0)))
I haven't tested this to make sure the parentheses are all right, but at worst, this should be very close.

 

[LiveToExcel]

BiocideJ,

Great catch . . . I was unsure whether there were formulas in the B5 & C5 cells. Thanks for clarifying for Padthelad.

LiveToExcel