GTretick
Member
I'm not sure if such a thing is possible but I thought it would be handy to have a formula sum data differently depending on where I am on the spreadsheet.
What I am working on is a budgeting worksheet were I want to budget work hours by employee. I will have different sections of the spreadsheet where I will be accumulating hours by work function (say direct work, planning, sales admin etc). See Primary sheet tab on attached sheet
I would have a list of employee names down the rows. Say rows 10:20. My input into those rows would be say hours in each month Columns C:N for Sales.
As I am in row 10 for "Joe Smith" a formula in row 5 would total up all budgeted hours for Joe based on similar inputs for the other categories (work, planning etc) to give me some context of what we have done with him so far.
I could build a separate table (See rejected option 1 tab on attached sheet) to do this for each employee but then I am using a lot of spreadsheet space and losing focus on the employee I am currently working on. The employee list could be quite long.
The idea is when I move down to row 11 for "Mary Jones" then row 5 would be replaced with all of Mary's accumulated data to date.
I could build all the functions for Joe and Mary into the same work area (see Rejected option 2 in attached sheet) so that the first 3 rows are all Joe ( Panning, Sales, Admin) and then three more for Mary etc. but then I would lose context as to what is happening for the sale effort etc.
The key would be some sort of formula or function is cell A5 that would know I what my active cell was (say I was clicked onto cell C10) the return value would be Joe based on Excel recognizing I was in row 10 and giving me whatever the value was in column A. (see my mock formula in A4).
Hopefully there is something out there that does this. I'm not really strong in VBA but I will try to muddle through if that is where this leads.
Thanks.
What I am working on is a budgeting worksheet were I want to budget work hours by employee. I will have different sections of the spreadsheet where I will be accumulating hours by work function (say direct work, planning, sales admin etc). See Primary sheet tab on attached sheet
I would have a list of employee names down the rows. Say rows 10:20. My input into those rows would be say hours in each month Columns C:N for Sales.
As I am in row 10 for "Joe Smith" a formula in row 5 would total up all budgeted hours for Joe based on similar inputs for the other categories (work, planning etc) to give me some context of what we have done with him so far.
I could build a separate table (See rejected option 1 tab on attached sheet) to do this for each employee but then I am using a lot of spreadsheet space and losing focus on the employee I am currently working on. The employee list could be quite long.
The idea is when I move down to row 11 for "Mary Jones" then row 5 would be replaced with all of Mary's accumulated data to date.
I could build all the functions for Joe and Mary into the same work area (see Rejected option 2 in attached sheet) so that the first 3 rows are all Joe ( Panning, Sales, Admin) and then three more for Mary etc. but then I would lose context as to what is happening for the sale effort etc.
The key would be some sort of formula or function is cell A5 that would know I what my active cell was (say I was clicked onto cell C10) the return value would be Joe based on Excel recognizing I was in row 10 and giving me whatever the value was in column A. (see my mock formula in A4).
Hopefully there is something out there that does this. I'm not really strong in VBA but I will try to muddle through if that is where this leads.
Thanks.