+ Reply to Thread
Results 1 to 14 of 14

reference cells problem

  1. #1
    Registered User
    Join Date
    04-04-2018
    Location
    Egypt
    MS-Off Ver
    2010
    Posts
    16

    Question reference cells problem

    Hello Everyone,

    I am designing an Excel sheet at my work.
    I have a sheet of 5 cells table which is my daily testing sheet, in which I type the 5 results everyday.
    then there is another excel sheet which is the monthly sheet that has all the records of the month ( which contains 100 cells table, the total number of the whole daily tests)
    So everyday I copy 5 results and then the following 5 results..etc to the monthly sheet.
    In order to save time of copy/past results from the two sheet, I made a reference cell in order to type once only in the daily testing ( 5 records sheet), but the results will show only on the 1st 5 cells in the 100 cells monthly sheet.
    I want to be able to write the first 5, then when I come for testing the following day it should copy the new results in the following 5 not in the same 5 I refered to.

    will I be able to make a register sheet that refer to another excel sheet and results shows in sequence ?

    Help me plz, Thanks in advance
    Attached Files Attached Files
    Last edited by Silex7; 05-12-2018 at 10:17 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: reference cells problem

    Attach a sample workbook (not image).

    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.

  3. #3
    Registered User
    Join Date
    04-04-2018
    Location
    Egypt
    MS-Off Ver
    2010
    Posts
    16

    Re: reference cells problem

    Hello Johnn,
    Sorry for the inconveniences,

    I attached an example of what I want to do.

    Thank you again for the clarification
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: reference cells problem

    You can not do that using formulae. You will either need VBA, or re-think how you want to achieve the desired result. Using a formula, once you delete the values in one sheet, they will vanish from the other sheet, too.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Registered User
    Join Date
    04-04-2018
    Location
    Egypt
    MS-Off Ver
    2010
    Posts
    16

    Re: reference cells problem

    Quote Originally Posted by Glenn Kennedy View Post
    You can not do that using formulae. You will either need VBA, or re-think how you want to achieve the desired result. Using a formula, once you delete the values in one sheet, they will vanish from the other sheet, too.
    Thank you Glenn for your reply, so I should move this thread to VBA section i guess.
    If someone could gently move my thread to the VBA forum I will be very thankful.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: reference cells problem

    Please Login or Register  to view this content.
    Cick on Daily tab, "View Code" and Copy/Paste Code above

    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
    [B][I]

    Monthly data will be updated when 5th entry is completed: remove old entries using ClearContents.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-04-2018
    Location
    Egypt
    MS-Off Ver
    2010
    Posts
    16

    Re: reference cells problem

    Quote Originally Posted by JohnTopley View Post
    Please Login or Register  to view this content.
    Cick on Daily tab, "View Code" and Copy/Paste Code above

    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
    [B][I]

    Monthly data will be updated when 5th entry is completed: remove old entries using ClearContents.

    Dear John, Thank you so much!!

    Yes thats it indeed.. it really works

    I really appreciate your help!!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: reference cells problem

    As a newcomer to the forum, a gently reminder ....

    Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

    For normal conversational replies, try using the QUICK REPLY box below.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: reference cells problem

    Re your PM:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    04-04-2018
    Location
    Egypt
    MS-Off Ver
    2010
    Posts
    16

    Re: reference cells problem

    Quote Originally Posted by JohnTopley View Post
    Re your PM:

    Thank you so much John, really appreciated!

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: reference cells problem

    Re-open thread (as it is marked as SOLVED) if you need further help.

  12. #12
    Registered User
    Join Date
    04-04-2018
    Location
    Egypt
    MS-Off Ver
    2010
    Posts
    16

    Re: reference cells problem

    Quote Originally Posted by JohnTopley View Post
    Re-open thread (as it is marked as SOLVED) if you need further help.
    Thank you John,
    Yes, I just need to ask about the Range part in the first code ("b10:B10") what does this range refers to?( I mean here the small 'b' letter distracted me .

    What would I do if I had to set range for rows? (for example check attached file).

    And in the second code,

    in worksheets 1 & 2 , Just the columns "B" and "C" would be replaced with the desired ones right? ( No change in i+ 1r)?

    Again appreciate your help.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: reference cells problem

    Change to ...

    Worksheet_Change

    If Not Intersect(Target, Range("E6:E6")) Is Nothing Then


    and in

    Update_Monthly


    ws2.Cells(i + lr, "C") = ws1.Cells(6, i)

  14. #14
    Registered User
    Join Date
    04-04-2018
    Location
    Egypt
    MS-Off Ver
    2010
    Posts
    16

    Re: reference cells problem

    Thank You John so much, now it really worked ,
    I will remake it solved
    So much thanks indeed

+ 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. Replies: 5
    Last Post: 05-03-2016, 05:01 AM
  2. Replies: 7
    Last Post: 02-03-2013, 06:25 PM
  3. reference problem
    By Alexander_Golinsky in forum Excel General
    Replies: 1
    Last Post: 05-10-2012, 04:06 PM
  4. Replies: 6
    Last Post: 11-11-2011, 06:32 AM
  5. [SOLVED] Reference to cells using autofilter problem
    By Twitos in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-14-2006, 04:10 PM
  6. [SOLVED] Reference problem
    By RB Smissaert in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-03-2005, 06:15 PM
  7. [SOLVED] Reference cells from another worksheet problem
    By Neil in forum Excel General
    Replies: 3
    Last Post: 06-23-2005, 12:24 PM

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