Possible IF function???

[CTaylor1968]

I have a spreadsheet consisting of 5 worksheets. One of these worksheets is my basis for recording vacancies and when those vacancies are filled. At the moment I'm doing this manually. For example I have a column of establishments and then next to that is the column telling me how many vacancies there are in that establishment. Each time we appoint somebody to that establishment I manually go in and reduce the number by 1. Is there a simple IF formula to do this. So, for example:- Column A has the establishment; column b the number of vacancies and column G would say Y (yes for filled vacancy) or N (for not filled). Sometimes there may be more than one vacancy so I would then need it to subtract 1 from column b if column G says Y (eg).

Secondly, I record information on one worksheet and would like to have that information to hand on a second worksheet. If I update worksheet 1 can I automatically update worksheet 2 with the updated info?

Sorry it's long. I'm trying so hard to get to grips with Excel. I know about some functions such as validation etc, but I'm still trying to learn the IF functions etc....how do you guys get to know these formulae???

Thanks very much, as always.

 

[macropod]

Hi C,

In order for Excel to do what you want, it would need to have the number of available positions in each establishment, or at least some way of calculating it (eg a column listing the positions in the establishment). It's not clear from your post whether those requirements are met. If they are, and you could provide a fuller description of your data layout, I'm sure something could be worked out fairly quickly.

As for replicating values on one worksheet in another, that's easy: simply select the cell where you want the replicated results to appear, press the '=' symbol, then navigate to and select the source cell and press <enter>. There are fancier ways of doing this to handle missing values, columns/rows being deleted, and so on, but the above should get you started.

Cheers

 

[xplo5iv]

Hi

I think from what your saying you need to be using COUNTIF
for your vacancy total? It would then count all the Y entries.

Or have I got the wrong end of the stick?

 

[xplo5iv]

Hi again

Just a thought - following on from macropod

If you there are not that many cells on your master sheet that need to be carried forward, name them individually first before creating the links. This has two advantages: -

1)Deleting columns etc does not screw the whole thing up.
2)Looking at the formulae on the 'child' sheets gives more information - providing the naming on the master sheet makes sense!

The disadvantage is that you can't use Paste Link to paste a whole column.

In case you weren't aware, the box at the top left lets you name the selected cell(s)

HTH

Jonathan

 

[CTaylor1968]

Thanks Macropod. There is a list in column b of the number of vacancies that there are currently. I have a second worksheet for resignations so each time I record a resignation, I want column B to add that new resignation to the total. So for example column b would give the number of vacancies that are there at the moment (eg 2). Then when a resignation is recorded in the vacancies worksheet column b would automatically increase. I then have a third worksheet for appointments made and each time an appointment is recorded on that worksheet I want the number of vacancies to reduce by 1.

I do appreciate the help - thank you.

 

[macropod]

Hi C,

In terms of needing a fuller description, I'd need to know more about the layout of each sheet. I can't get a clear understanding of how the data are organised from your descriptions so far. I especially don't understand how your column G relates to column B for multiple vacancies.

For example, you might have a Summary sheet that looks something like:

Col A			Col B		Col C	Col D		Col E	Col F	Col G
Establishment	Vacancies	staff	Positions					Filled?
EstablishmentA	2			3		5
EstablishmentB	0			5		5
EstablishmentC	1			4		5

I think the first two columns here match your description of the worksheet that has "a column of establishments and then next to that is the column telling me how many vacancies there are in that establishment".

Then you say you have "a second worksheet for resignations", a "vacancies worksheet" and "a third worksheet for appointments". What do these sheets look like? So far you seem to have mentioned 3 or 4 of the 5 you originally said you have.

The way to get the answer you're after depends on what you're working with.

Cheers

 

[CTaylor1968]

Okay. Didn't realise how hard it would be to explain
The spreadsheet has a number of worksheets. One of these worksheets will be used to collect the data from other worksheets and its own worksheet to give me an ongoing summary of vacancies.

The summary sheet has 8 columns (A-H) as shown below:-

A B C D E F G H
Establishment Vacancies Appntd

Col B gives the original number of vacancies. When the appointments panel meet and make their appointments, column H would show a "Y" for yes. In that instance, column B would reduce by 1 for the specific establishment.

Now, the remaining worksheets also contain relevant information that would have an impact on column b. When a person resigns and this is recorded in the resignations sheet, column b for the relevant establishment would increase by 1. EG, if the resignations sheet shows a resignation at Bexhill High School, column b (cell relating to Bexhill) will reduce.

Is this making sense?

 

[macropod]

Hi C,

From you description of your summary sheet, it sounds as if:
. each row represents one establishment and all the staff in it.
. Column H (previously G?) will always have either 'Y' or 'N'.
. Column B will have a number, that increases by 1 every time a resignation is recorded in the resignations sheet, but decreases by 1 every time 'Y' is entered in Column H.
What I can't understand from this is how can you record in column H that 2 or more vacancies have been filled. Equally, if your resignations sheet has only one row per establishment, I can't see how you can keep track of positions being filled/vacated over time.

Cheers

 

[CTaylor1968]

I think it's all too complicated and I should stick to filling it in manually.

Thanks anyway - much appreciated.