+ Reply to Thread
Results 1 to 12 of 12

VBA: Dynamic cell referencing

  1. #1
    Registered User
    Join Date
    09-04-2012
    Location
    Coventry, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    VBA: Dynamic cell referencing

    Hello all,

    I'm hoping someone can point me in the right direction. Here's my challenge.
    I have a SharePoint list export showing some requested course submissions. In my excel workbook I have the SP export on one sheet, and on another sheet I have a vlookup showing some of the data pulled from the SP list based on the course name. What I would like to do is then write some data either back to either the SP list or another area (not fussed about that) based on that selection - the idea being a sort of tracker.
    That's it in a nutshell. Linking cells doesn't work as it's based on the vlookup, any ideas greatly received. Thanks

    Chris
    Last edited by AliGW; 08-06-2019 at 11:18 AM.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Dynamic cell referencing

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,070

    Re: Dynamic cell referencing

    1. Formulas cannot "push" data anywhere, they can only use data that "pull" from other cells.
    2. You cannot have data AND a formula in the same cell, 1 will replace the other.

    So, if that was what you were aiming for, you would need VBA for that. If I read you wrong, please try explaining again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    09-04-2012
    Location
    Coventry, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamic cell referencing

    Hi FDibbins,

    Yes this is what I was aiming for any idea how best to go about it?

    Chris

  5. #5
    Registered User
    Join Date
    09-04-2012
    Location
    Coventry, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamic cell referencing

    Hi Olly,

    I've just tried to post with attachment it didn't let me, said I need to post a few times so hopefully I'll be able to update this thread with it shortly.

    Chris

  6. #6
    Registered User
    Join Date
    09-04-2012
    Location
    Coventry, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamic cell referencing

    Hi all,

    This is a desensitised version of the workbook.

    The aim is for a user to go to the 'Update' sheet, select their course name. This populates basic course info in top section and any existing data in the bottom sections. They then should be able to update the middle section and comments box. This is where I'm stuck, this data should then write back to the 'Source Data' sheet so that it is saved for then next time they need to update. The 'overview' and 'lists' sheet isn't required for this stage.

    Any ideas how I could do this?

    Thanks

    Chris
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-04-2012
    Location
    Coventry, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamic cell referencing

    Sorry it took so long to respond, I've just got back from hols.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,270

    Re: Dynamic cell referencing

    If your intention is to use this form as a way to repeatedly populate new rows in your database, then you will need VBA. Shall I move the thread for you?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  9. #9
    Registered User
    Join Date
    09-04-2012
    Location
    Coventry, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamic cell referencing

    Yes please

  10. #10
    Registered User
    Join Date
    09-04-2012
    Location
    Coventry, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamic cell referencing

    Hi AliGW,

    Did you manage to move this to VBA thread?

    Chris

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,270

    Re: Dynamic cell referencing

    Well, I thought I had, but apparently not. I have moved it now. Sorry!

  12. #12
    Registered User
    Join Date
    09-04-2012
    Location
    Coventry, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: VBA: Dynamic cell referencing

    Still stuck on this if any one has any ideas?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Trouble referencing dynamic cell data from another worksheet
    By AlexMetric in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-22-2015, 09:22 AM
  2. [SOLVED] Referencing File Path - Dynamic Cell Input for Worksheet
    By Chomie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-23-2013, 04:38 PM
  3. [SOLVED] Countif function referencing a dynamic cell
    By magnus.blomquist in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-07-2013, 06:22 AM
  4. VBA Dynamic Cell Referencing for Charts title
    By sanjeevpandey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-08-2013, 11:14 AM
  5. Dynamic Cell referencing in range function for vba in excel
    By karunakaranindia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-23-2010, 01:30 AM
  6. Excel 2007 : Dynamic cell referencing
    By ozfader in forum Excel General
    Replies: 3
    Last Post: 10-11-2010, 11:59 PM
  7. cell referencing from a dynamic location
    By djrivum in forum Excel General
    Replies: 0
    Last Post: 01-21-2005, 12:58 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1