Dynamically Named Range

[MrDB4Excel]

Without using VBA I would like to have a formula to first select all cells in columns A thru E where data exists while disregarding all blank rows below this data. Then with this selection current run the copy as picture routine which is a 2 pick from the menu. First from the Menu's Clipboard's "Copy" selection, then the "Copy as Picture" selection, ultimately choosing from the popup the "Picture" Format selection.
If the copy as picture portion is not doable in a formula that is fine, I would continue using the selection buttons as usual. The most important part is somehow with a formula to select the total range inclusive of data throughout columns A thru E that from time to time may include more or less rows than shown in this example.
In thinking about this it occurs to me that a named range that dynamically changes the range based on the number of rows containing data in columns A thru E is somehow key to this formula working to select the range.
Shown below is a link set to select the data in the named range A_New_Data.

Thanks to anyone who can help me solve this puzzle.

DynamicNamedRange.xlsx
	A	B	C	D	E
1	ReceivedTime	SenderName	subject	To	Doc #
2	08/21/2021 10:02:09	Sender A	Subject 1	Sender B	1 >
3	08/21/2021 10:28:21	Sender B	Subject 2	Sender A	2 >
4	08/21/2021 17:17:55	Sender A	Subject 3	Sender B	3 >
5	08/21/2021 17:45:05	Sender A	Subject 4	Sender B	4 >
6	08/21/2021 19:06:10	Sender A	Subject 5	Sender B	5 >
7	08/21/2021 22:25:17	Sender B	Subject 6	Sender A	6 >
8	08/22/2021 10:13:10	Sender A	Subject 7	Sender B	7 >
9
10	Without using VBA I would like to have a formula to first select all cells in columns A thru E where data exists while disregarding all blank rows below this data. Then with this selection current run the copy as picture routine which is a 2 pick from the menu. First from the Menu's Clipboard's "Copy" selection, then the "Copy as Picture" selection, ultimately choosing from the popup the "Picture" Format selection. If the copy as picture portion is not doable in a formula that is fine, I would continue using the selection buttons as usual. The most important part is somehow with a formula to select the total range inclusive of data throughout columns A thru E that from time to time may include more or less rows than shown in this example. In thinking about this it occurs to me that a named range that dynamically changes the range based on the number of rows containing data in columns A thru E is somehow key to this formula working to select the range. Shown below is a link set to select the data in the named range A_New_Data.
11
12
13
14
15
16
17
18
19
20
21			Get A1 to E Last Row Of Data
ExampleData

 

[Alex Blakenburg]

I found the easiest to be this:

• Set up the camera tool on your quick access toolbar
  • File > More > Options > Quick Access Toolbar
  • Choose commands not in ribbon > Camera
• convert your data into a table
• Under Table Design > Table Name - give the table a meaningful name
  I called mine tblData
• Under range names.
  Pick a Range Name (I called mine PictureLinkToTable) and put the you table name into the address box in this format.
  =INDIRECT("tblData"&"[#All]")
• Select the whole table
• Click on the camera tool
• Navigate to where you want the table
• Select an area which will drop in the picture
• With the picture selected go to the formula bar and type in
  = YourRangeName (in my case = PictureLinkToTable)

Camera icon is:-

 

[MrDB4Excel]

I found the easiest to be this:

• Set up the camera tool on your quick access toolbar
  • File > More > Options > Quick Access Toolbar
  • Choose commands not in ribbon > Camera
• convert your data into a table
• Under Table Design > Table Name - give the table a meaningful name
  I called mine tblData
• Under range names.
  Pick a Range Name (I called mine PictureLinkToTable) and put the you table name into the address box in this format.
  =INDIRECT("tblData"&"[#All]")
• Select the whole table
• Click on the camera tool
• Navigate to where you want the table
• Select an area which will drop in the picture
• With the picture selected go to the formula bar and type in
  = YourRangeName (in my case = PictureLinkToTable)

Camera icon is:-

View attachment 45379

Thanks Alex. So far I am stuck at "Under range names". I cannot seem to locate this in my menu bar. Perhaps you are referring to the Name Manager under the Formulas tab?

 

[Alex Blakenburg]

I am. Ctrl+F3

 

[MrDB4Excel]

Thanks, I have my range as a named range but I am not understanding what the purpose is of the formula =INDIRECT("tblData"&"[#All]").
Adding that formula to a blank cell returns #VALUE.
Am I supposed to add that formula somewhere specific?
I did the following steps and it seems to work when I added lines or removed lines to or from the table:

• Select the whole table
• Click on the camera tool
• Navigate to where you want the table
• Select an area which will drop in the picture
• With the picture selected go to the formula bar and type in
  = YourRangeName (in my case = PictureLinkToTable)

 

[Alex Blakenburg]

I am in Australia and about to shut down.
When I did it you needed to have a name in name manager with an address
= Indirect(“yourtablename”&”[#All]”)
With your picture selected there will be a formula in the formula bar.
Replace that with = yourrangename

if you don’t do that, yes the existing formula will reflect changes BUT it won’t reflect additional rows added to the table.

 

[MrDB4Excel]

I am in Australia and about to shut down.
When I did it you needed to have a name in name manager with an address
= Indirect(“yourtablename”&”[#All]”)
With your picture selected there will be a formula in the formula bar.
Replace that with = yourrangename

if you don’t do that, yes the existing formula will reflect changes BUT it won’t reflect additional rows added to the table.

Okay Alex, Now I understand. Thanks loads.

 

[Alex Blakenburg]

No problem. Thanks for the feedback and glad I was able to help.