Extended Excel query ranges.

[BajanPOET]

When I set up an external query in Excel and run it it always shifts my document in some way, making my cell references in my formulae incorrect.

I realize that when I click on the name of the named ranges created by the External queries, the range is longer than it should be - for example, when the wizard asks me where I want to place the result of the query and I say F16, the named range starts at F16, but also includes F17, F18 and F19 as well. How can I specify just F16 as my range? Or somehow reduce the range to only include F16 rather than the other 3 cells?

GOD is in charge, though men may say, "Not so!

 

[SkipVought]

If you have formulas in cells adjacent to your querytable, they can AUTOMATICALLY adjust to the varying length of the querytable resultset...

Data/Data Range Properties -- Check the box at the bottom.

Skip,

_[red][/red]

 

[BajanPOET]

But I don't have the box checked... I don't *want* the range extended. I just want the Requisition Number to go into F16.

GOD is in charge, though men may say, "Not so!

 

[SkipVought]

1. Only ADD the querytable ONE TIME.

2. The get new data, hit Data/Refresh

3. Reference the individual cell you need.

Skip,

_[red][/red]

 

[BajanPOET]

I know what the problem was -
When I edited the query, there were 3 options for this statement:
"If the number of rows in the data range changes upon refresh:
1. Insert cells for new data, delete unused cells.
2. Insert entire rows for new data, clear unused cells.
3. Overwrite existing cells with new data, clear unused cells."

1. is the default -and was the source of my problems. Coz it always looked like a row was being deleted all the time. I changed the option in both my queries to option 3 - overwrite and clear without deleting any rows - and the problem was solved.

Thanks!

GOD is in charge, though men may say, "Not so!