G&T - Get Data from Range without converting it to a Table?

Melimob

Active Member
Joined
Oct 16, 2011
Messages
364
Hi

I thought I had a simple question but can't seem to find the answer on the internet..

I have a range which I don't want to convert to a table because it will frustrate other users of the file.

I want to be able to get data from this worksheet/range but it always converts it to a table to do so?

Is there any way round this?

Many thanks
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,075
define name and use it in new Query, eg.
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="[COLOR="#0000CD"][B]DefName[/B][/COLOR]"]}[Content]
in
    Source[/SIZE]
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,075
U'r welcome

btw. use Reply not Reply With Quote
 
Last edited:

peter789

Board Regular
Joined
Nov 20, 2016
Messages
114
Another sneaky way on a simple spread sheet is to set your range to the print area (this works well if you are importing from multiple identical spread sheets e.g. if each year/region etc is on separate tabs or even workbooks). This is a predefined range and can be loaded into power query.
If the size of your data range is likely to expand go old school and set it to a dynamic range. An Internet search will quickly lead you to a number of ways of achieving this.
Peter
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,075
with multiple sheets define names (or print area) are not necessary because PQ recognize these ranges itself as sheet names
 

Watch MrExcel Video

Forum statistics

Threads
1,096,068
Messages
5,448,196
Members
405,492
Latest member
DPuser

This Week's Hot Topics

Top