Rookie, needs a Macro or formula for if-then on 4 criteria

[JENESIS9777]

Hello
I have "IF" data in 1 column and I need to populate the "THEN" into another column. I am creating an index, and it would be easier to have it auto populate than to type everything in.

Column N = Document type
Column L = Index Number code

for example I need to determine

if contract then =1.0
if agreement or work order= 1.1
if supporting document= 1.1.1
if other = 1.1.1.1

and I have to run a pivot table report off of that data to see how many of each for certain time periods (year to date and weekly and monthly) I do have a date column in this spreadsheet as well. I may also need to sort by vendor as well. I am constantly adding data to this sheet.

Thank you for reading and for any help I get!!

 

[edvwvw]

"=LOOKUP(LEFT(A7,1),{"a","c","o","s";"1.1","1.0","1.1.1.1","1.1.1"})"

Assumes that the IF result is in A7.

edvwvw

 

[jamieleeuk]

Hiya,

I noticed no one has answered this yet and whilst there are experts on here who will poss come to this later with a one liner solution to your issue, I do know a way to get you to your goal (provided I have understood correctly).

Firstly, make a list of the Document Types somewhere in your worksheet / workbook (doesn't matter where). Highlight that list and click on "Insert > Name > Define" and call it DocType.

In cell L2 (assuming L1 is already used for a column header) click on Data > Validation and choose "List". In the Source box type =DocType and press OK

In cell N2 (again assuming N1 is used for column header) type =IF(L2="Contract","1.0",IF(L2="Agreement","1.1",IF(L2="Work Order","1.1",IF(L2="Supporting Document","1.1.1",IF(L2="Other","1.1.1.1","")))))

You can drag that down however many rows you need to and that should do the trick until someone with a wee bit more knowledge and a smaller solution comes along.

Best Regards
Jamie

 

[JENESIS9777]

Hi and thank you for the response, however I tried the formula as written, then I tried to modify it as needed, still no luck.

I even cut the column L and moved it to N so it could reference the info to the left. I pasted what I have and I have an #N/A error. what should I do?

=LOOKUP(LEFT(M:M),{"CONTRACT","EXHIBIT","SUB-AGREEMENT","SOW","SUPPORTING DOCUMENT","OTHER";"1.0","1.0","1.1","1.1","1.1.1","1.1.1.1"})

 

[jamieleeuk]

Hi,

so you're not waiting round for me to get back to you I thought I would reply now. Other than nesting loads of IF statements like I mentioned in my reply above I am totally lost and cannot help you further so best waiting for someone else other than me to come back to you - but if no one else does, I have tested the above and it works with up to 7 doc types.

Good Luck!

 

[Special-K99]

=LOOKUP(M1,{"CONTRACT","EXHIBIT","SUB-AGREEMENT","SOW","SUPPORTING DOCUMENT","OTHER"},{"1.0","1.0","1.1","1.1","1.1.1","1.1.1.1"})

and copy down column M

 

[edvwvw]

Hi

I see that the if result is in column M:

You need to look at a specific cell in the column - the expression LEFT was used to look for the first letter in the IF result - you do not need the whole word/ sentence.

If you really want to use the whole word then you could drop LEFT and just use M1 or M2 etc

The first part of the lookup array {"contract", "Other",etc etc } should be arranged alphabetically otherwise it will produce the wrong result




edvwvw

 

[JENESIS9777]

Jaime your a Genius! So are the rest of yoou that responded! Thank you it worked great!!! you saved me hours of work! THANK YOU THANK YOU!!

THANK YOU EVERYONE FOR YOUR RESPONSES I THINK THIS ONE IS SOLVED! (i have another post i never received a response on if there are any takers ) Have a great day everyone!