Using value from an existing cell inside a formula

Question

I am using the "Camera" feature in excel 2010.

My goal is to have a cell which would have a date that I can manually enter and just below it the formula would get the updated value which represents the name of the sheet inside another excel file and shows me the updated screenshot.

For example: Cell A1 has: 12.25

Just below it, I have: ='C:\My_Excel_Files\[excelDataFile.xlsx]12.25'!$A$1:$D$20

So if I were to change the value of Cell A1 to 12.26 the formula would pick that up automatically.

I tried using the indirect function like this: ='C:\My_Excel_Files\[excelDataFile.xlsx]INDIRECT(A1)'!$A$1:$D$20

but it didn't work.

Answer 1

yeah, this is pretty straightforward. create two xlsx documents, let's call them s1.xlsx and s2.xlsx. it's from s1 that we want to create the Camera, but the data is in s2.

in s2

• with both sheets open, create what you want in s2. then select it all and press the "copy" button.

in s1

• then go back to s1 and wherever you want to put it, select the top left cell of what you'll be pasting.
• then go to the paste button, but click it's dropdown. the second icon will be a chain link and if you hover it says "paste link". do that.
• then select all your data you want (including the pasted link cell(s)) and press the camera button (assuming you've added it to your QAT). then select where you want to put your camera screenshot on s1 anywhere and click there. the screen shot will be created.
• (Note: the pasted link will look something like =Excel.Sheet.12|'C:\Users\Me\Desktop\s2.xlsx'!'!Sheet1!R3C1:R4C2')

in s2

• then go back to s2 and change any of the values there in the linked cell(s)

in s1

• you'll see the values reflected in the screen shot in s1 immediately.

Answer 2

As mentioned on this forum and this website, you can use the free MOREFUNC.XLL add-in. It provides the INDIRECT.EXT function that will do exactly what you're requesting. The formula you need in your case would be along the lines of:

=INDIRECT.EXT("'C:\My_Excel_Files\[excelDataFile.xlsx]" & A1 & "'!$A$1:$D$20")

Where you concatinate the value of A1 in the middle of the string.

Note: the links in those sites seem to not always work, you can either google for "MOREFUNC download" or try one of these links:

http://download.cnet.com/Morefunc/3000-2077_4-10423159.html
http://www.freewarefiles.com/Morefunc_program_14922.html

Answer 3

A slightly different approach might work for you. Let's assume that:

1. the Camera image object is called MyCameraImage
2. the "other" workbook is called SO09.xlsm
3. the range your image corrresponds to is $F$1:$I$5 and is the same on all relevant sheets
4. the cell you type the date into (in the worksheet with the camera image) is A1

Add a Worksheet_Change event to the worksheet with the camera image

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wbOther As Workbook
    Dim wsOther As Worksheet

    If Not Intersect(Target, Me.[A1]) Is Nothing Then
        Set wbOther = Workbooks("SO09.xlsm")
        On Error GoTo EH
        Set wsOther = wbOther.Worksheets(CStr([A1]))
        Me.Shapes("MyCameraImage").DrawingObject.Formula = "='[" & wbOther.Name & "]" & [A1] & "'!$F$1:$I$5"
EH:
    End If

End Sub

This will update the Camera Image reference to the sheet named in cell A1 (if it exists)
Note that the "other" workbook must be open for this to work and for the image to update