Solved: Adding more functionality

[Aussiebear]

Currently this speadsheet works fine, however I was wondering if its possible to add another function to the workbook.

I would like to be able to change one value for a contract status and have it find all relevant vendor Dec numbers and override the existing formula if possible

For Example, should the value of cell B3 on sheet change from "No" to "Yes",for say contract T2330, could a devised function then find all values which start with the value T2330 on the sheet "Whiteboard' ( in this case Cells B2 & B8) and have it retain a priority call to change the value in column D of their respective rows? I still would like the existing formula in Column D of the first sheet to be active as well, but only whilst the value of cells in column B on the Contracts sheet retain the value of "No"

Is this a simple case of nesting the existing IF statements inside another IF, or should I be looking at a Vlookup type function, or conditional formatting perhaps?

Ted

[LitVilkas]

Why don't you just add an extra column in sheet 'whiteboard' (e.g. in column C) with the contract no (=left(b2;5)?
Then you can use 'countif' in the 'contracts' sheet.

[Aussiebear]

For the sake of brevity, I omited on the Contracts sheet 2 columns which I use to keep track of the total tons received and the balance outstanding. It is imperative that I continue to use this main sheet (Contracts) for the aforementioned purpose. What I am hoping is that when a contract is completed I can simply change the value in Column B of the Contract Sheet and this will then change the value Column D of all vendor's who are part of the Contract.

How does CountIf help me here?

[LitVilkas]

Sorry, my mistake - I think I didn't quite get what you need. Essentially it's still a formula issue, not something to utilise VBA for.

Check whether
=IF(B2="";"";IF(ISERROR(VLOOKUP(LEFT(B2;5);Contracts!A:B;2;0));IF(G2<0,01;"";IF(G2>F2;"Y";"N"));IF(VLOOKUP(LEFT(B2;5);Contracts!A:B;2;0)="no";IF(G2<0,01;"";IF(G2>F2;"Y";"N"));"Y")))
inserted in D4 ('whiteboard') solves your proplem.
It's 'if' / 'vlookup' combination you mentioned.
You first check if there is data in your row at all. You then check, if you contract no appears in 'contracts' (if not, you previous formula applies). Subsequently you read out the contract status for each contract no. If anything than "No", it'll put a "Y", otherwise your previous fomula is applied.

Hope I understood you right now.

[Aussiebear]

A bigger contract may have up to 45 vendors supplying into this contract. If a contract number is dispalyed as T4352 then the vendors could have a number anywhere from T4352-01 to T4352-45. Will your formula still find these vendors?

[Aussiebear]

Okay, have installed and it appears to work, but the formula is far too complex for me to understand. Can you break it down into segments so that I can get my head around it please?

[LitVilkas]

=IF(B2="";""; - checks if there is data in your row at all; that way you can already copy the formula into empty rows

IF(ISERROR( - checks if the vlookup, looking for a contract no in 'contracts' returns an error value (i.e. doesn't find the formula)

VLOOKUP(LEFT(B2;5);Contracts!A:B;2;0)) - the vlookup just mentioned, of which

LEFT(B2;5) - takes the T4352 out of T4352-01 to T4352-45 - i.e. functions as lookup_value

IF(G2<0,01;"";IF(G2>F2;"Y";"N")) - your formula, applies if there in a contract no in 'whiteboard' but not in 'contracts'

IF(VLOOKUP(LEFT(B2;5);Contracts!A:B;2;0)="no" - if there is a contract no, and it's value is set to "no", then

IF(G2<0, 01;"";IF(G2>F2;"Y";"N")) - your formula kicks in again


"Y" - otherwise you'll have a Y in your filed.

[Aussiebear]

Excellant. Thankyou for your assistance in this matter. One thing that has just occurredto me is that you use semi colon's rather than comma's in your formula. When I copied your formula into the cell, I was given an error message. When I replaced the semi's with comma's all worked.

Ted

[Aussiebear]

In moving this formula

"=IF(B2="","",IF(ISERROR(VLOOKUP(LEFT(B2,5),CONTRACTS!A:B,2,0)),IF(G2>0.01,"",IF(G2>F2,"Y","N")),IF(VLOOKUP(LEFT(B2,5),CONTRACTS!A:B,2,0)="N",IF(G2<0.01,"",IF(G2>F2,"Y","N")),"Y")))

to the Company's spreadsheet I've encountered a problem.

Can someone kindly explain the VLookup section, and in particular the table array address (Contracts!A:B,2,0.)

I'm assuming that Contracts! refers to the Sheet not a defined range, and that A:B is the range on that particular sheet. The value 2 is the Column numbers and the value 0 is the Range lookup value.

If the real data currently lies on the Contracts Sheet, Range B3:C150,
Would the corrected section of the formula read,
"Vlookup(Left(B2,5),Contracts!B:C,2,0)"

Ted

[mdmackillop]

Hi Ted,
You have a space in 0.01, but I'm not getting the correct results (as I understand them)
try =IF(ISNA(VLOOKUP(LEFT(B2,5),Contracts!A:B,3,0)),"",IF(VLOOKUP(LEFT(B2,5),Contracts!A:B,2,0)="Yes","Y",IF(G2<0.01,"",IF(G2>F2,"Y","N"))))

Edit; Posting here is inserting blanks, in this case within "Contracts"

[Bob Phillips]

Excellant. Thankyou for your assistance in this matter. One thing that has just occurredto me is that you use semi colon's rather than comma's in your formula. When I copied your formula into the cell, I was given an error message. When I replaced the semi's with comma's all worked.

Ted

That is because LitVikas is Lituanian, and in Lithuania they use the continental notation, semi-colon for separators not comma, etc.

[Aussiebear]

Malcolm, I appreciate your efforts here but confusion reigns supreme...


In revision, If a Contract is considered "closed", i.e. if it's value on Sheet "Contracts" Col B is "Yes" then it needs to find every vendor dec ID (e.g T2330-01, T2330-02 etc) that is related to the main contract number T2330 and override the formula that exists in Col D of Sheet "Whiteboard". Otherwise the formula- listed below- that was in Col D should take precedence.

"=If(G2<0.01,""',If(G2>F2,"Y","N")".

This formula is based on the fact that a vendor dec e.g T2330 may be filled but the overall contract may still be open.

In the post that reopened this thread I needed to know about the Vlookup function and in particular the section which calls for the table array address.

Does the section (Vlookup(Left(B2,5),Contracts!A:B,3,0) where it describes the Contracts! A:B,3,0) refer to the Sheet Contracts, and that the range on the sheet is in the columns A:B, and does the value "3" refer to the Col_Num, and value 0 refer to the Range Lookup.

In your bottom section of the revised Ver 4 spreadsheet your formula returns "Y" for T4312 when it should be a "N". Neither the vendor dec is closed ( Its tonnages received does not exceed the Vendor dec tonnage) nor is the contract closed ( Column B on Sheet "Contracts").

The deleted section from the original formula " IF(G2>0.01,"", "which follows the initial Vlookup request is what is required to trigger the "N" value I believe.

Anyway I have to rush off to work now, being "late" is a hanging offence.

Ted

[Aussiebear]

Yep, thanks XLD. I had his formula working well once I transposed his semi colon's for comma's. I only ran into a problem once I tried to change the formula when I typed his into the Company sheet where the Contract data resides on the Contracts sheet but in the range B3: C150

[mdmackillop]

Hi Ted,
The last sample is from your post, and I agreee does not work. My formula in rows 22 - 28 is IO think returning the correct solution.
Regards
Malcolm

[Aussiebear]

Malcolm, sorry but no. Row 25 should be a "N" as some tons have been delivered against the Vendor Dec. Should only be a blank if a vedor dec is entered and no tons have been received.

Ted

[Aussiebear]

or no vendor dec is entered... sorry about the slip up here

[mdmackillop]

Hi Ted,
It is blank because there is no applicable contract number on Contracts. This is what the Error Handling does.

[Aussiebear]

Righto, I shall report to the firing squad immediately. Farewell my friend.

but as a last request what about my post in #9. If the Company data resides in the range B3:C150 on the sheet "Contracts" do I change the "A:B" section of the vlookup formula to B:C?

If I'm going to die, I like to be less confused about this point.

[mdmackillop]

Yes. Use Inset/Function for guidance on inputing data, arrays etc.

[Aussiebear]

Thankyou. There was a volley of shots a couple of minutes ago, but it seems they missed.

Second volley has just been heard and I have a red stain.... Crikey I've been hit.


"You lousy shots" here give me that I'll do it myself.....

[mdmackillop]

Sometimes you need to stand back and look at the whole project. If this is all about record keeping, there is a lot to be said for doing this sort of thing in Access. It's much more powerful and secure for storing and analysing data, especially in a "shared" situation. It maybe will probably require a custom application, but that could be the way to go.

[Aussiebear]

You're right about the need for something in Access but.... I am intimidated by Access. While I know next to nothing about Excel, and even less about VBA, I know even less about Access despite having done a Cert IV course. The instructor advised us to not even think about vba for Access.

So I sort of built an anti Access mindset.

Mind you I'm open to suggestions, but they are going to have to be good ones.

[mdmackillop]

My suggestion.
Create a detailed brief of your project; what are your objectives; who will interact with it; and so on. Once you know what you want, there's a better chance a programmer could implement it.

[Aussiebear]

Yes I understand your request but history will show that while I started with a simple spreadsheet to record vendor grain deliveries, over time I saw a need for something to make it more funtion friendly.

(And a chance to impress the employer)

[mdmackillop]

It's not so much a request, but more a reminder to revisit your projects occasionally and decide whether your current approach is still the best, especially as your VBA skills improve!