Formula that changes what cells it pulls from based on where active cell is

GTretick

Member
Joined
Jul 29, 2015
Messages
42
Reaction score
0
Points
6
Location
Canada
Excel Version(s)
365
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.
 

Attachments

  • Floating Formula Reference.xlsx
    14.9 KB · Views: 9
The attached has a formula in cell A5:
=INDIRECT("A" & CELL("row"))
Excel's Help says that it returns the information "for for the last cell that was changed"
So for this to work, you have to change a value on the row you're interested in. This can be just going into a cell to edit it, but not edit it, and press Enter.

The above is an answer using just a formula, but it's not terribly good - but might just good enough.

However, the file also has a macro in it, which as the file stands, does absolutely nothing:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Range("A6").Value = Cells(Target.Row, 1).Value
End Sub
This is because the single line in the macro has been commented-out by an apostrophe at the start of the line.
This is an alternative solution to the one above and is used on row 6 of the sheet. When the selection is changed on the sheet the macro is triggered.
To make this macro actually do something you have to activate that single line; here's how:
Right-click on the sheet tab and choose View Code.
A new window should appear with the code above visible.
Delete the apostrophe.
Close the new window.
The macro is now active.

As soon as you move the selection on the sheet you should see cell A6 update as well as its dependants on the same row.

Coincidentally it also causes A5 to update and I have no idea why!
 

Attachments

  • ExcelGuru4748Floating Formula Reference.xlsm
    15.6 KB · Views: 15
Last edited:
Both those solutions worked quite well for me. I'm not too worried about the input stipulation in your first suggestion as the output is for reference while entering a line of data. It should be easy to work with.

The second solution works even better however I'm not sure if I know enough VBA to apply this in several areas. For example I don't see the name of the tab in the code so I don't know how I would apply it if working in several tabs. No need to reply on this point unless you're feeling like somebody else might benefit from that answer.


Incidentally, this is my first post on this site. Do I need to do anything to mark this thread as answered or does it sit as open ended?
 
For example I don't see the name of the tab in the code so I don't know how I would apply it if working in several tabs
You'd have to apply it to every sheet you wanted it to work on, there's a clue when I asked you to "Right-click on the sheet tab and choose View Code". It is specific to that worksheet.


Incidentally, this is my first post on this site. Do I need to do anything to mark this thread as answered or does it sit as open ended?
I can't find any 'Mark as Solved' options, so I'm guessing you leave it open ended? I suppose you could add a message that it's solved but I think you have effectively done that already!
 
I have a follow up question.

It seems that when the VBA is present, I cannot use the undo button on the tab that the coding exists on.

Can this be fixed or do I have to live with this?
 
Can this be fixed or do I have to live with this?
You have to live with this. (Actually you don't, but it's a convoluted solution)
 
Back
Top