Excel Assistance - IF statements

[nickpowell]

hi there,

Im pretty new to using excel. I know how to do the basics but need a bit of help with a document im trying to create. This will probably be the first of many questions.

Anyway, the excel document is made up of 2 or more seperate sheets. The first sheet being a "summary" sheet and the others being input data "resource roles". I run a resourcing and recruitment company so essentially what I want to do is as follows:

* there will be a column in each of the input data sheets that has a drop down menu for each cell of that column with values "Open" or "Closed"
* In the Summary Sheet I want to copy certain information from the other sheets to make a summary of all the "open" roles and ignore the "closed" roles.
OR
alternatively
* I want when the cell in the input data sheets is set to "closed"....that entire row will be deleted then all open roles - certain info will be copied to the summary sheet.

im not sure which is the best way to do this. Essentially we get resource requirements daily that we work and these requirements expire/close so they should be taken off the workbook. I also basically want 1 summary sheet that I can print out daily that we can use as a consolidated requirements list instead of going through each sheet.

The input data sheets have the following columns:
* Company Name
* Role / Position
* Duration
* Location
* Salary
* Contact Person
* Email Address
* Closing Date
* Candidates Sent
* Candidates Contact Number
* Candidate email
* ANT rate
* Candidate rate
* Candidate availability
* Sourced By
* Feedback
* Spec Status (Open or Closed drop down)

The summary sheet will contain only the following data pulled from the input sheet if the role is still open
* Company Name
* Role/Position
* Duration
* Location
* Salary
* Contact Person
* Email Address
* Closing Date

Please let me know what would be the best way to do that and if you can hold my hand through the forumulas that would be greatly appreciated.

Kind Regards

Nick

[Mick9999]

Nick

If I understand it, you have a detail sheet with multiple rows of individual records and you want to summarise this data in another sheet.

Can you clarify why you have more than 2 worksheets.

[nickpowell]

hi there

attached a sample excel document of what im working with.

thanks

Nick

[Russell Dawson]

Hi Nick

Welcome to the forum. Why do you need a summary sheet? Does each summary sheet represent one of the records from the data sheet? Have you already prepared a workbook. It would help if you could upload a representative sample of that with fictitious details. Sometimes it's best to explain what your overall goal is and the reasoning behind it so that we can understand and perhaps offer alternative suggestions to what you already envisage.

Regards

Russell

Edit - sorry Mick9999 - didn't see your response.

[nickpowell]

Wow quick response Mick.

Well basically we have seperate worksheets for each client .... the only reason we have the client/company name column on the input data sheets is so that we can pull the info through to the summary sheet however if its possible to pull the name of the input data sheet to the summary sheet that would work as well.

but what you have said above: detailed sheets with multiple rows of individual records that need to be summarised into another sheet (only if the roles are open).

Edit:

Russell - theres only 1 summary sheet but multiple datasheets

[Russell Dawson]

If I understand correctly then, your summary sheet is fed by the data on each input data sheet only when spec status states "Open" in the data input sheet.

Seems to be the wrong way round. It would be better to have a summary sheet or master with all your data on that can feed the others. Have you considered just one database sheet and then using filters to manipulate the info?

Whilst I remember, it's not really a good idea to have your summary sheet with merged cells for data. It's OK for headers etc but ideally you should have a clean page of data with no merge and no gaps. If you have to have more than one "Candidate" I would change that to separate columns each. It will avoid problems say if you need to extract data for individual candidates later in development.

Food for thought?

Cheers

Russell

[nickpowell]

If I understand correctly then, your summary sheet is fed by the data on each input data sheet only when spec status states "Open" in the data input sheet.

Seems to be the wrong way round. It would be better to have a summary sheet or master with all your data on that can feed the others. Have you considered just one database sheet and then using filters to manipulate the info?

Whilst I remember, it's not really a good idea to have your summary sheet with merged cells for data. It's OK for headers etc but ideally you should have a clean page of data with no merge and no gaps. If you have to have more than one "Candidate" I would change that to separate columns each. It will avoid problems say if you need to extract data for individual candidates later in development.

Food for thought?

Cheers

Russell

Hi Russell,

You are correct in the requirement but the summary sheet wouldnt be the master sheet.

Essentially the summary sheet is just to give my consultants a high level list of requirements that are currently out.

The other input data sheets are used more for candidate tracking rather than spec tracking. Whose interviewed where and the outcomes, agreed rates/margins, etc.

hope this clarifys

thanks

[nickpowell]

by the way I agree with the "no merge" statement.... basically just copied another sheet of ours that had merged cells but they are not required.

[Russell Dawson]

Sounds like now we have a master sheet, a summary sheet and subsequent multiple data input sheets. It really would help to see exactly what the workbook looks like and what we are working with. If it's large take a copy and delete all data except for a few lines. Being careful with sensitive info.

I think that what you ask for originally may need VBA which is not my field.

[nickpowell]

nope not a master sheet as each input sheet holds unique info. Just a summary sheet and multiple datasheets for each client.

I did attach the excel sample doc above....

edit: multiple data sheets being a different sheet for each different client. Sorry the above might have sounded confusing