N
NiFreaky
I haven't done any Excel spreadsheets before. I have now set up a ne
department in work and have created a spreadsheet that is used fo
logging correspondence that comes into the office. There was a bi
backlog and I have split the spreadsheet into 2 sheets, one for on
type of correspondence and the other for another type. There is abou
130 rows in one and 50 in the other.
Each of these rows include 15 columns that include reference numbers
names, addresses, dates etc. There is no calculation type data. th
last 2 columns are general text fields, one (Column L) that says wher
the resulting file has been sent to and one (column N) that says th
file is now complete/actioned/no further action, etc.
Every 2 weeks I have to compile a report stating 1. How many are stil
to be looked at. 2. How many are sent to other departments but no
finished. 3. How many are outstanding. 4. Total actioned out of all th
rows on both sheets.
What I am trying to do is create a third sheet that has all th
calculations on it and display the report. I can do this manually b
adding autofilters and firstly filtering the blanks from N then th
blanks from L. This gives me the figure reqired at 1. above. After tha
it gets complicated!
I have looked at =countif and =rows without much success. From readin
these forums I think =sumproduct might be a better choice but I ain
got a clue how to use it.
Also, do I just set the whole column in the calculation i.e. N:
because I am always adding new items of correspondence? Or is there
way to automatically increase the active area everytime I add a ne
row?
Phew, that was complicated to explain. It would be so much easier t
show someone who knew what they were doing
department in work and have created a spreadsheet that is used fo
logging correspondence that comes into the office. There was a bi
backlog and I have split the spreadsheet into 2 sheets, one for on
type of correspondence and the other for another type. There is abou
130 rows in one and 50 in the other.
Each of these rows include 15 columns that include reference numbers
names, addresses, dates etc. There is no calculation type data. th
last 2 columns are general text fields, one (Column L) that says wher
the resulting file has been sent to and one (column N) that says th
file is now complete/actioned/no further action, etc.
Every 2 weeks I have to compile a report stating 1. How many are stil
to be looked at. 2. How many are sent to other departments but no
finished. 3. How many are outstanding. 4. Total actioned out of all th
rows on both sheets.
What I am trying to do is create a third sheet that has all th
calculations on it and display the report. I can do this manually b
adding autofilters and firstly filtering the blanks from N then th
blanks from L. This gives me the figure reqired at 1. above. After tha
it gets complicated!
I have looked at =countif and =rows without much success. From readin
these forums I think =sumproduct might be a better choice but I ain
got a clue how to use it.
Also, do I just set the whole column in the calculation i.e. N:
because I am always adding new items of correspondence? Or is there
way to automatically increase the active area everytime I add a ne
row?
Phew, that was complicated to explain. It would be so much easier t
show someone who knew what they were doing