Cell reference of cell last in list?

[I-Like-Excel]

Hello,

I think I may have been able to do this once before but I think I have forgotten.

How do you call out the cell reference (not the value) of the last cell in say a table of values so that you can then use that cell reference to drive another equation?

Thanks in advance

[Tony Valko]

Need more specific details.

Is it a 2 dimensional range (multiple rows and columns)?

A single row or column?

What type of data is it? Text? Numeric? Could be both? Something else?

How do you want to use this cell ref in another formula?

[FDibbins]

What exactly are you trying to do?

[I-Like-Excel]

The actual table of values I am referencing has multiple columns and rows but for the sake of defining the table size, we can look at the last column and the last row with data in it.
All cells are actually equations that either return a text or numerical value of are blank "". All data outside the table will display "".

So lets assume column G is the last column of in the table and row 53 in the last data entry in that column, then the cell that I am trying to reference in another equation would be G53.

However if via changes in driving data elsewhere now make the last data entry in column G to appear in row 88, then the cell reference appearing in that other equation would be G88.

The actual equation I am using the cell reference in is actually the one used to define a table in a Picture
eg. Sheets1!$A$2:$G$88

where $G$88 is evaluated

[I-Like-Excel]

What exactly are you trying to do?

Hi again!

It actually is related to my other post about the Database design.

Having now got all my "reports" that reference the Master table, I am now using Copy, Save As Picture to generate a view of the reports on each sheet. However I want the picture to dynamically reference the size of the report generated on each sheet and resize itself accordingly as data is added/removed from each report.

NOTE: to have the Picture dynamically linked to the data you click on the picture and in the formula window add the table range. I want this table range (and hence the size of the picture) to update to suit the size of the table however.

Hope this makes sense.

[FDibbins]

Will this do what you want - for the 2nd ref, anway...
="B"&SUMPRODUCT(--(A1:A20<>""))

[I-Like-Excel]

I was unable to insert that equation and get it to function as desired for example, in to the Picture table equation.

The highlighted part of the equation needs to be replaced by an equation that dynamically will return the last cell reference of the table if the table changes size.

pic.JPG

PS: edited to attach file to forum.

[FDibbins]

Can you upload the file to the forum please? I am unable to access dropbox from here (work)
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[I-Like-Excel]

Done...edited previous post.

[Tony Valko]

Why is the range A1:B6?

Don't you just need the range of the numeric data in A3:B6?

Will there be empty cells within this range? Is this a possibility:

Data Range

	A	B
1	------	------
2		95
3	23	76
4		70
5	50	13
6	76

Which is the last non-empty cell?

Is the data ALWAYS numeric?

[I-Like-Excel]

Think of the Picture as being a portable "data panel" which needs to be viewed elsewhere.

In the example you have given, the Picture window definition will be =$A$1:$B$6 for example.

If row 5 and 6 was blank then it would be =$A$1:$B$4.

What is shown by the Picture panel is not really that important. What is important is that the picture panel reference can be controlled by the size of the table.

Note that the "start" of the table reference will always remain the same and essentially is not that important whether it is A1 or A3.
Only the "end" of the table is important to dynamically define so that the Picture frame is correctly sized if the table gets bigger or smaller.

[FDibbins]

Im not sure how you get that reference into the pic, but this will essentially give you the same reference...
="=A1:B"&SUMPRODUCT(--(A1:A20<>""))

[I-Like-Excel]

Im not sure how you get that reference into the pic, but this will essentially give you the same reference...
="=A1:B"&SUMPRODUCT(--(A1:A20<>""))

Yes we now have a solution for the last table cell reference. Now we just need to somehow use that to drive the Picture definition...

PS: I don't actually understand what this notatio is: --(A1:A20<>"")

....or think of another way to drive the Picture definition

Similar to what I had done earlier, using filters:

ex.JPG

This is a bit clunky because if you edit the data in the Master table, the filtered tables do not update automatically. Not really a preferable solution.
picture table2.xlsx

[FDibbins]

Now we just need to somehow use that to drive the Picture definition...

Thats the part I dont understand. How did you get that into the "pic" in the 1st place?

[FDibbins]

sorry, i forgot this part...
SUMPRODUCT(--(A1:A20<>""))

A1:A20<>""
This is testing for all cells that are not blank, it will return a list of TRUE and FALSE. This needs to be converted to numbers, so we use a double negative...
--(A1:A20<>"")
This produces a list of 1 (TRUE) and 0 (FALSE), which we then need to add...
SUMPRODUCT(--(A1:A20<>""))

[I-Like-Excel]

Thats the part I dont understand. How did you get that into the "pic" in the 1st place?

It appears to be a little known yet immensely powerful Excel feature.
Select any part of a workbook.
Go to Home, Copy, Copy as Picture
Then Paste (this basically creates like a static screenshot of the section of worksheet selected)
To make it a live dynamic screenshot, click on the picture and press F2
Now select/re-select the part of the workbook you want it dynamically linked to and press Enter.
Viola!

[FDibbins]

OK, I could not get that to work, but neat trick

Im wondering if my formula could be built into some VBA code to automate this. I have just started learning VBA, and its getting late here. I will try and play with this tomorrow - and also call in some of the VBA big guns to help

[Kyle123]

Can't you just use the camera tool? Pretty sure you can use a formula for that

[Crooza]

Wow . Worked for me. That is really cool

[I-Like-Excel]

Can't you just use the camera tool? Pretty sure you can use a formula for that

Seems that the instructions I gave are essentially what the Camera Tool does in one click. I was not aware of the Camera Tool. The end results are the same thing: a picture dynamically linked to a worksheet range. Thanks for bringing it up anyway.

Now how do we get the picture range to dynamically adjust to the size of the table?

[Kyle123]

You want to create a named range with the formula:

Please Login or Register  to view this content.

Then set the named range as the formula of the picture

[I-Like-Excel]

You want to create a named range with the formula:

Please Login or Register  to view this content.

Then set the named range as the formula of the picture

That's the shot! I was toying around with named ranges but now we have nailed it! Thanks all again!

[snb]

If you would have made a table (insert / table) (VBA: listobject) for range A1:C41 the cameratool would have done this automatically (no named rnage necessary).