+ Reply to Thread
Results 1 to 6 of 6

How to pass cell address to a ROW function on different worksheet

  1. #1
    Registered User
    Join Date
    01-16-2018
    Location
    UK
    MS-Off Ver
    2016
    Posts
    3

    How to pass cell address to a ROW function on different worksheet

    Hi all. New user here needing for help. Also not versed well in excel but can follow the instructions.

    Overview: I've attached my workbook here which has 3 worksheets - "Main" with rota and duty no , "Duty No" with duty numbers and start + end times, "GCal" sheet is where I want duty no & times to export to google calendar. Rota numbers start from 1 to 62 and each lasts for a week starting from Saturday. On "Main", I've transposed the values for row table but currently all formulas are used on column based table. I'm not sure which is better suited or easier to work with but anyhow row based table is only for information.

    There are 2 macros on main: Dates & Duties. Dates takes start and end dates from F2+F3. This macro autofills the dates on "GCal" B2 Column. Duties button autofills column G (Description) with Duty number on GCal from G3 based on G2.. Macros are only for testing at the moment but will further explore later and definitely will want to implement on the final workbook.


    Issues: There're few but as I'm newbie will want to go one at a time.

    1) I've used combination of address & match functions to retrieve desired row no on main sheet (F4). I want to pass this address eg A15 to a ROW function on GCal sheet (cell G2) which has =INDEX(ARRAY,ROW(here),2). I tried pointing to sheet!F4 but it's returning value from row 3 and not from row 15.

    Thanks.
    Attached Files Attached Files
    Last edited by ghostivv; 01-20-2018 at 03:31 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Help with work Rota

    Title updated...
    Last edited by protonLeah; 01-20-2018 at 03:21 PM.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    01-16-2018
    Location
    UK
    MS-Off Ver
    2016
    Posts
    3

    Re: How to pass cell address to a ROW function on different worksheet

    I think I've got it now. I replaced INDEX() with OFFSET() on GCal and getting the same results as before. Will post updated file later for checking.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How to pass cell address to a ROW function on different worksheet

    G2 should be:
    Please Login or Register  to view this content.
    The MATCH function is meant to return the relative position of an element in list.
    If you want to drag it down, try:
    Please Login or Register  to view this content.
    RotaStart = '53Main'!F1
    Last edited by protonLeah; 01-20-2018 at 04:41 PM.

  5. #5
    Registered User
    Join Date
    01-16-2018
    Location
    UK
    MS-Off Ver
    2016
    Posts
    3

    Re: How to pass cell address to a ROW function on different worksheet

    Thanks protonLeah. Yes both your functions are working perfectly. I've added another criteria (day of the week) to find the exact row for duty number. With 2 criteria, your function looks like =INDEX(DutyNo,MATCH(1,INDEX((RotaNo=RotaStart)*(Days=DayToStart),0),0)+ROW()-2,2). =OFFSET('53Main'!A1,RotaStartRow,1) is much easier to follow (at least for me) but needed 2 more extra functions to find out row no and start day. Not sure if any disadvantages using OFFSET.

    As the issues is now solved, do I need to edit title again to move on to next problem or should I start new thread?
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How to pass cell address to a ROW function on different worksheet

    This thread should be marked as "solved" and start a new thread.
    Please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Create Work Rota
    By Taylor_Jane in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-26-2015, 12:14 PM
  2. [SOLVED] Work Rota system- reading text to work out hours
    By cubb in forum Excel General
    Replies: 10
    Last Post: 12-05-2014, 09:59 PM
  3. Trying to setup a Rota for my Work. Help
    By Sm1th in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-09-2014, 09:45 PM
  4. Trying to work out a rota, can anyone help?
    By Paul Armstrong in forum Excel General
    Replies: 0
    Last Post: 05-22-2012, 02:26 PM
  5. Work Rota
    By warrell in forum Excel General
    Replies: 6
    Last Post: 03-25-2010, 11:01 AM
  6. holiday/work rota
    By YYX99 in forum Excel General
    Replies: 0
    Last Post: 06-06-2007, 11:01 AM
  7. Work Rota - Do I need a formula?
    By dataheadache in forum Excel General
    Replies: 11
    Last Post: 10-03-2005, 06:05 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