help with if then

isacp

Board Regular
Joined
Dec 16, 2004
Messages
135
i have a sheet
a b c d
ph jp phone shoes 5
st jp phone shoes 7
ma jp phone shoes 4
ph sr store shoes 6
st sr store shoes 7
ma sr store shoes 8

column a tells me were there order was made
column b is the salesman name
column c tells me their dept.
d the amount sold.
(in reality the list has about 200 rows and 5 more columns of numbers

i need to have a code that tells it to look in column c and if the word phone is in the cell then to add the # in the row of "ma" to the row with "Ph"
(ph stands for phone, st = store, and ma=mail)
the idea is that a phone person might have worked in the store so those numbers are different. but the ma should be added to their normal position ie. phones or store.

this is very lenghty so please feel free to ask any q i need help
my boss wants this yesterday
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi there,

Can you give a couple of real life examples? Can you post with the HTML Maker?

From what I understand, you are not looking for a formula, but to actually alter the data that you already have in your cells?
 
Upvote 0
I'm trying to create a formula that solves a problem with database information.

I have a 3 columns, first 2 with job classifications and department classification and the third column with person's age.

I'm trying to create formulate whereby I can select 2 scenarios in the first 2 columns e.g. select all people who are mechnical and work in the CR & C mechanical depart, and then show the minimum and maximun ages of those 2 scenarios from the ages recordedin the 3rd column.
 
Upvote 0
firefytr said:
Can you give a couple of real life examples?

..

From what I understand, you are not looking for a formula, but to actually alter the data that you already have in your cells?
 
Upvote 0
I have a database with 3 columns, one with employees Dept, one with his job classification, one with his age. In another worksheet I have 2 drop down box where I can select a combination of employee dept and employee job classiffcations, e.g. "electrical dept" and "engineers". The drop down box choices are linked to a cell on the worksheet. In another cell I want to work out 1. the Minimum ages of engineers in Electrical dept and the maximum age of engineers in the electrical dept. I'm trying to create a formulat that looks at the results in the cells linked to the dropdown boxes and then gives me the min/max results from the ages of the relevant dept/job classification that are in the database. e.g. of the formula I have created is as follows
=MAX(IF(Database!$E$4:$E$128=$S$3, IF(Database!$G$4:$G$128=$T$3, Database!$J$4:$J$128, 0))).
In this formual I'm asking to look at S3 and T3 (which have the dropdown box selections), go the to database ranges, and choose the max ages for the S3/T3 choices. The maximun seelsm to be working but when I try and get a minimum age it keeps returning "0". Am I on the right track? Hope this makes sense to you and thanks you so much for your help
 
Upvote 0
Hi Sue,

Without seeing your data, I would assume ..

Max:
=MAX(IF((Database!$E$4:$E$128=$S$3)*(Database!$G$4:$G$128=$T$3),Database!$J$4:$J$128))

Min:
=MIN(IF((Database!$E$4:$E$128=$S$3)*(Database!$G$4:$G$128=$T$3),Database!$J$4:$J$128))


Of course, confirmed with Ctrl + Shift + Enter, as they're array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top