+ Reply to Thread
Results 1 to 10 of 10

Moving a cell reference using a formula and a variable?

  1. #1
    Registered User
    Join Date
    07-05-2016
    Location
    New England
    MS-Off Ver
    2010
    Posts
    5

    Moving a cell reference using a formula and a variable?

    Hi,

    I have a advanced question that I cannot wrap my head around. Looking at the attached picture you can get a feel for what I am trying to accomplish. Where it says input I want to be able to type a number from 1-12 and have the cells in row 16 (Columns C/D respectively) return a specific cell reference as pertains to the input. For example if I input a 1 I want cell C16 to return a cell reference to the matching month (January). I am sorry if I explained this poorly it is quite difficult to understand what I am trying to do. Any help would be greatly appreciated!

    Thanks,
    Amitus

    Capture.PNG

  2. #2
    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,901

    Re: Moving a cell reference using a formula and a variable?

    Hi, welcome to the forum

    Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    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

  3. #3
    Registered User
    Join Date
    07-05-2016
    Location
    New England
    MS-Off Ver
    2010
    Posts
    5

    Re: Moving a cell reference using a formula and a variable?

    I uploaded an example book. What I am trying to do is on the sheet "CONTROL" where it says input I would type a number from 1-12. This would then prompt the cells in row 394 of sheet "Vacaction Current" to match the input number, which is basically a month i.e. 1 would be January, and create a cell reference to the corresponding row. Again if I explained that poorly please let me know and I will try to be more clear. Thanks again for the help.
    Attached Files Attached Files

  4. #4
    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,901

    Re: Moving a cell reference using a formula and a variable?

    where it says input
    I dont see that?

  5. #5
    Registered User
    Join Date
    07-05-2016
    Location
    New England
    MS-Off Ver
    2010
    Posts
    5

    Re: Moving a cell reference using a formula and a variable?

    I apologize, instead of input look where it says Period on the sheet "CONTROL".

  6. #6
    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,901

    Re: Moving a cell reference using a formula and a variable?

    If you are dealing with dates, it is almost always better to use real dates, rather than month names. Dates are values, and you use math functions on them - names are text, and most math functions cant "add" text

  7. #7
    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,901

    Re: Moving a cell reference using a formula and a variable?

    Not really sure what column you wanted, but see iof this will get you started...
    =INDEX('Vacation Current'!$E$381:$E$392,CONTROL!C5)

  8. #8
    Registered User
    Join Date
    07-05-2016
    Location
    New England
    MS-Off Ver
    2010
    Posts
    5

    Re: Moving a cell reference using a formula and a variable?

    In my case I do not believe the date should matter. The listed months on "Vacation Current" can be thought of as placeholders for the numbers 1-12. By typing in the Period number, 1, I want the cell in C394 to say "=C381" which can be thought of as the month of January if you please. So to continue, if I typed a 2 into the Period, the cell in C394 would change to say "=C382" due to the fact that I am now referring to the month of February. I want this to be done with a formula, but I am sure I could use a macro. Was this explanation a bit easy to understand?

  9. #9
    Registered User
    Join Date
    07-05-2016
    Location
    New England
    MS-Off Ver
    2010
    Posts
    5

    Re: Moving a cell reference using a formula and a variable?

    I was trying to accomplish this task by using a VLOOKUP by having the number in Period be the lookup value. This value would then be used to match which row I want the cell reference to refer to. What I mean by this is when I type that 1, that means I want the row "January" and I want my cell in C394 to state "=C381". If I instead type a 2 then its referring to the row "February" and would state in cell C394 "=C382". Hopefully my further explanations are narrowing this down haha. Thank you for your time FDibbins.

  10. #10
    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,901

    Re: Moving a cell reference using a formula and a variable?

    Did you try my suggestion?

+ 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] Replacing formula with $ variable cell reference
    By PQK78 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-17-2015, 12:14 AM
  2. [SOLVED] Reference a variable cell in a SUM formula based on values in a different column
    By sshot1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-17-2013, 12:40 PM
  3. How do I add a variable row number into a SumIf formula cell reference
    By Whit01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2013, 06:19 AM
  4. VBA Formula with an variable absolute cell reference?
    By kestefon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-19-2013, 01:13 AM
  5. [SOLVED] How to set formula with cell reference with a variable
    By momotaro in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-13-2013, 04:19 AM
  6. Formula to reference a specific variable cell
    By Spellbound in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2009, 07:59 AM
  7. Trying to 'write' a formula in a cell with a variable reference
    By stumpy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2007, 09:33 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