+ Reply to Thread
Results 1 to 5 of 5

Cell refrencing another sheet using variables

  1. #1
    Registered User
    Join Date
    05-30-2020
    Location
    London
    MS-Off Ver
    windows 10 / office 2016
    Posts
    2

    Cell refrencing another sheet using variables

    I am attempting to reference another sheet within excel which i know is the function =sheet1!(cell)
    But I wish to automate the possess using from a match function.

    I would like it to look something like this = sheet1!$B(=MATCH(2, Sheet1!A1:A55, 0))
    where the =MATCH(2, Sheet1!A1:A55, 0) is the row reference.

    Is there any way to do this.
    I have attached an example of what I have tried to do.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Cell refrencing another sheet using variables

    A3 =1

    C3 ="Sheet1!B"&Match($A3,Sheet1!$A$1:$A$55,0)

    D3 =offset(INDIRECT(C3),4,4,,)

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    05-30-2020
    Location
    London
    MS-Off Ver
    windows 10 / office 2016
    Posts
    2

    Re: Cell refrencing another sheet using variables

    Thank you for your response but I am looking to get the text that followed the item number, I understood what you did and modified it so that it offsets it by 0,0 which works.
    I understand that I did not clarify this.

    But thank you very much.

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Cell refrencing another sheet using variables

    In B16 (Sheet2):

    =INDIRECT(CELL("address",INDEX(Sheet1!B:B,MATCH(Sheet2!B15,Sheet1!B:B,0))))

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Cell refrencing another sheet using variables

    Or -
    if you add the number of your item in A15 -

    you can use this formula in B15:
    =INDIRECT(CELL("address",INDEX(Sheet1!B:B,MATCH(Sheet2!A15,Sheet1!A:A,0))))
    Attached Files Attached Files

+ 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. Help with sheet name refrencing
    By almst791 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-12-2017, 04:31 PM
  2. [SOLVED] Refrencing a cell in an inserted sheet
    By PLD60 in forum Excel General
    Replies: 3
    Last Post: 01-24-2017, 06:40 PM
  3. [SOLVED] Cell Refrencing multiple sheet totals to a totals page
    By 007Buff in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-08-2015, 02:23 PM
  4. Using variables to populate sheet and cell references in another sheet
    By TLW80 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-02-2014, 08:20 PM
  5. [SOLVED] Locating a cell in sheet 2, based on 2 variables in sheet 1. Result into sheet 1.
    By Gezza24 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 11-05-2013, 12:50 AM
  6. [SOLVED] IF statement for multiple cells refrencing one cell
    By ProSpiderman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-17-2013, 02:08 PM
  7. Refrencing a variable cell in a formula
    By C Brehm in forum Excel General
    Replies: 2
    Last Post: 10-10-2005, 11:05 AM

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