Results 1 to 8 of 8

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

  1. #1
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,807
    Articles
    0
    Excel Version
    365
    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!
    Attached Files Attached Files
    Last edited by p45cal; 2015-07-30 at 02:56 AM.

  3. #3
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365
    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?

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,807
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by GTretick View Post
    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.


    Quote Originally Posted by GTretick View Post
    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!

  5. #5
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365
    Thanks for the help.

  6. #6
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365
    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?

  7. #7
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by GTretick View Post
    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?
    You should start your own topic and ask a question.
    I must mention that after running VBA macros can not go back, so you can not use the UNDO command after run VBA,
    There are small exceptions but it is irrelevant, in my opinion
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,807
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by GTretick View Post
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •