+ Reply to Thread
Results 1 to 9 of 9

Text Box - How to reference cell in current sheet

  1. #1
    Registered User
    Join Date
    08-04-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Text Box - How to reference cell in current sheet

    Hello Everyone,

    I have created a sheet ('Picture-Shapes') which has 10 different shapes. These shapes are then called on in other sheets by referencing them as pictures using this method: http://excel.tips.net/T003128_Displa..._a_Result.html

    This is working well but now i would like to add dimensions to the pictures by referencing cells. I have achieved this using text boxes on the original sheet with the shapes ('Picture-Shape') but I can only reference cells from the same sheet. I want to reference cells from the active sheet so that when the image is used on any sheet it uses data from that active sheet.

    Is this possible using formula?

    Would a Label be another option?

    Thanks for your help,
    Andrew

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Text Box - How to reference cell in current sheet

    Hi,

    I've read your last paragraph several times, and even though I'm also Down Under, I can't make sense of it. Can you post a sample file and explain in context?

    cheers, teylyn

  3. #3
    Registered User
    Join Date
    08-04-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: Text Box - How to reference cell in current sheet

    Hi Teylyn,

    Thanks for your reply - I don't seem to be able to upload the file at the moment - i have uploaded here though:

    https://www.dropbox.com/s/h6tr4vnpt8xizch/Dynamic%20TextBox%20Name%20Sample.xlsm?dl=0


    Thanks,
    Andrew

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Text Box - How to reference cell in current sheet

    I got the file from Dropbox, thanks.

    The textboxes on the Pictures sheet won't "know" what sheet they are being displayed on as an image, so changing them dynamically will not be possible.

    But you could place a new text box on the sheet Door A and link it to cell AM18. Add text boxes to the other sheets in the same way.

  5. #5
    Registered User
    Join Date
    08-04-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: Text Box - How to reference cell in current sheet

    Hi Teylyn,

    Thanks for you reply.

    I have already been able to to as you suggested but the problem with this is that I have to includ so many different text boxes to cover all of the images that might be placed on the sheet.
    I may have 20 - 30 image options so it would be great if the text boxes were actually added with the "master" pictures but the text in the boxes updated depending on the sheet the image is on.

    Regards,
    Andrew

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Text Box - How to reference cell in current sheet

    No, just one text box for each sheet. The text box overlays the dynamic image and reads the value from the current sheet.

    I have attached your file. I've removed the text boxes in the Pictures sheet and added a text box each to the Door A and Door B sheets, and linked those text boxes to the yellow cell.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-04-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: Text Box - How to reference cell in current sheet

    Hi Teylyn,

    Thanks for your spread sheet. I understand the method you have used (I have already tried this method also) and although this will achieve the result I need something which has the textboxes (or label) on the "pictures" sheet so that these pictures are the reference images which any sheet can call and then use the cell reference from that active sheet (so in my example if Door A and Door B sheets.)

    This is because each image will have 5 - 10 textboxes and these will be located in different locations on the picture. If I have to place all of these text boxes onto the sheets (Door A and Door B) there will be possibly hundreds of text boxes on each sheet.

    I need to be able to create the reference image on the "pictures" sheet with the textboxes defining which cell to read the data from based on active sheet. So the formula in the text boxes would be something like ='ActiveSheet'!$AM$18

    I have tried CAMERA function also but this doesn't seem to work also.

    Thanks again,
    Andrew

  8. #8
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Text Box - How to reference cell in current sheet

    That's not possible to do with dynamic images. A dynamic image, regardless whether it has been created with the Camera Tool or with Copy as Picture will always represent the cells of the source sheet, exactly as they are in the source sheet.

    If you have the same door on three different sheets, with three different values in cell AM18 there is no formula way that each sheet would show a different number in the text box if the text box is on the Pictures sheet.

    You may need to re-think your data architecture. Maybe text boxes on the pictures are not the way to go.

    Or you may move this to the next level and use VBA to populate cells and text boxes. It'll be a lot more involved, but with VBA almost anything can be achieved.

    Please don't follow up and ask how to do this with VBA. It will be a major project with many, many different aspects, which need to be planned in advance before any code can be written.
    Last edited by teylyn; 08-09-2016 at 05:47 PM.

  9. #9
    Registered User
    Join Date
    08-04-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: Text Box - How to reference cell in current sheet

    Thanks Tyelyn, Is there another way to acheive this besides VBA? Labels perhaps?

+ 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. VBA - Search Sheet 1 for text, reference corresponding cell, populate Sheet 2?
    By rdr06001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2016, 01:30 PM
  2. Replies: 8
    Last Post: 02-02-2016, 12:40 PM
  3. Indirect Sheet reference with cell reference left function and text
    By teststrip in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2015, 10:31 AM
  4. [SOLVED] Reference cell on another worksheet based on value on current sheet
    By clocke in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2014, 12:24 PM
  5. [SOLVED] save as with current filename plus cell reference text
    By AWITCHER in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-14-2014, 10:00 AM
  6. Current Sheet Tab name change to text in cell a1
    By robrobet in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2013, 11:22 AM
  7. [SOLVED] Reference of Previous sheet in current sheet cell.
    By ranjeet.bhagat57 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-25-2012, 11:37 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