Linking to Access data without destroying formulas

[Keith]

Greetings:

I have a 20+ sheet workbook with tens of thousands of formulas, many of which reference a "data" sheet in the workbook that contains data that was copied and pasted into it. Trying to get out of the daily copy and paste routine.

The data is in an Access db. I can get the external data, but when I push it in to the workbook's data sheet, the hardcoded data gets pushed to the right and all the formulas still refer to hardcoded data.

I want the external data I'm getting to overwrite the hardcoded data and have the multitude of formulas work the way they always have. Deleting the old data before doing the import breaks all the formulas. What am I missing?

TIA
Keith

 

[GS]

I'm curious as to why you chose to reply to existing posts by posting
new posts rather than simply replying to those existing posts. Keeping
subjects grouped under 1 topic heading makes it much easier to give
help because all replies fall under the same original question.

 

[Clif McIrvin]

Greetings:

I have a 20+ sheet workbook with tens of thousands of formulas, many
of which reference a "data" sheet in the workbook that contains data
that was copied and pasted into it. Trying to get out of the daily
copy and paste routine.

The data is in an Access db. I can get the external data, but when I
push it in to the workbook's data sheet, the hardcoded data gets
pushed to the right and all the formulas still refer to hardcoded
data.

I want the external data I'm getting to overwrite the hardcoded data
and have the multitude of formulas work the way they always have.
Deleting the old data before doing the import breaks all the formulas.
What am I missing?

TIA
Keith

I've done a little bit of linking to external data, but not much. What
exactly do you mean by "push it in to the workbook's data sheet"?

If you use Excel's tools to set up a link to external data nothing
should move around when you re-fresh .... of course, you have to account
for changing row counts when linking to external data. So ... my
limited experience suggests that you should, fairly easily, be able to
accomplish what you are asking.

Whenever I was linking to Access data I was running xl2003; now my
company has switched to xl2010 .... I can get the ribbon locations for
the tools in 2010 but I no longer have access to 2003.

 

[Clif McIrvin]

I'm curious as to why you chose to reply to existing posts by posting
new posts rather than simply replying to those existing posts. Keeping
subjects grouped under 1 topic heading makes it much easier to give
help because all replies fall under the same original question.

Not quite sure what you're asking, Garry. I see where Keith posted
replies in two other threads -- OE threaded both correctly. In fact,
the "InReplyTo" header seems to be formatted correctly, as far as I can
tell.

It does look like Keith is posting from Google Groups; from comments in
other threads recently that may be a factor.

 

[Clif McIrvin]

Not quite sure what you're asking, Garry. I see where Keith posted
replies in two other threads -- OE threaded both correctly. In fact,
the "InReplyTo" header seems to be formatted correctly, as far as I
can tell.

It does look like Keith is posting from Google Groups; from comments
in other threads recently that may be a factor.

Correction: Keith's posts do not contain a "References:" header at all;
Google Groups appears to be using the "In-Reply-To:" header instead.

 

[Clif McIrvin]

Not quite sure what you're asking, Garry. I see where Keith posted
replies in two other threads -- OE threaded both correctly. In fact,
the "InReplyTo" header seems to be formatted correctly, as far as I
can tell.

Now that there are more postings in this thread, I see the "posting to
new branch" behavior in OE as well.

I'll guess that I didn't notice the improper threading at first because
OE is only "kinda-sorta" a newsreader <grin>.

 

[GS]

Now that there are more postings in this thread, I see the "posting to new
branch" behavior in OE as well.

I'll guess that I didn't notice the improper threading at first because OE is
only "kinda-sorta" a newsreader <grin>.

Clif,
I'd expect that all news readers would comply with NTTP standard
protocols and so I disagree with your assessment of OE because I see
the same postings there as I do in T-Bird and MesNews.

 

[Clif McIrvin]

on 5/25/2011, Clif McIrvin supposed :

Clif,
I'd expect that all news readers would comply with NTTP standard
protocols and so I disagree with your assessment of OE because I see
the same postings there as I do in T-Bird and MesNews.

What I'm seeing in the OE threaded view is that a reply to the OP - even
if it has no "References:" header - is in fact indented at the first
level. It's not until there's a reply to a reply (w/o References that
I see the indentation indicate a new branch to the thread. That
suggests to me that there is something different in OE's default thread
processing than what you are seeing in either T-Bird or MesNews.

(Keith - my apologies for "hi-jacking" your thread. I did reply to your
OP.)

 

[GS]

I only see your's and my replies in this thread. All Keith's replies
show as separate threads beginning with "Re: " followed by the subject
of the original thread. None of these are indented and so display as
'new' threads in all my readers.

 

[Clif McIrvin]

I only see your's and my replies in this thread. All Keith's replies
show as separate threads beginning with "Re: " followed by the subject
of the original thread. None of these are indented and so display as
'new' threads in all my readers.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Reply from old Google Groups UI. Will this thread corectly?

Clif

 

[GS]

Reply from old Google Groups UI. Will this thread corectly?

Clif

Yes, it lists correctly.

 

[Clif McIrvin]

Yes, it lists correctly.

For possible future use, as posters who use Google Groups will most
likely be completely unaware of the threading issues the new Google
Groups causes for newsreaders, I put the following boilerplate together.

Posts from Google Groups seem to include this header:
Organization: http://groups.google.com


--
Notes on 25 May 2011

The "New Google Groups" UI does not thread correctly, but the old UI
still does.

From the old Google Groups UI there is a link to switch to the
"new Google Groups" but I am not aware of a link to get from the new
UI to the old UI.

I just spent a few minutes trying to learn how to get back to the old
google groups from the new groups .... and I didn't find a link to get
there from here.

The URL for the new UI looks something like this:
https://groups.google.com/d/forum/«groupname» or
https://groups.google.com/forum/#!forum/«groupname»

https://groups.google.com/group/«groupname»/topics

for example:
https://groups.google.com/group/microsoft.public.excel/topics

Using the 'group' (instead of the 'forum') form of the URL will take
you to the old UI, which as of May 2011 does thread correctly.


To be precise, the old UI includes the NNTP "References:" header, but
the new UI includes a "In-Reply-To:" header instead, which follows a
different format.

 

[Keith]

For possible future use, as posters who use Google Groups will most
likely be completely unaware of the threading issues the new Google
Groups causes for newsreaders, I put the following boilerplate together.

Posts from Google Groups seem to include this header:
Organization:http://groups.google.com

--
Notes on 25 May 2011

The "New Google Groups" UI does not thread correctly, but the old UI
still does.

From the old Google Groups UI there is a link to switch to the
"new Google Groups" but I am not aware of a link to get from the new
UI to the old UI.

I just spent a few minutes trying to learn how to get back to the old
google groups from the new groups .... and I didn't find a link to get
there from here.

The URL for the new UI looks something like this:https://groups.google.com/d/forum/«groupname» orhttps://groups.google.com/forum/#!forum/«groupname»

https://groups.google.com/group/«groupname»/topics

for example:https://groups.google.com/group/microsoft.public.excel/topics

Using the 'group' (instead of the 'forum') form of the URL will take
you to the old UI, which as of May 2011 does thread correctly.

To be precise, the old UI includes the NNTP "References:" header, but
the new UI includes a "In-Reply-To:" header instead, which follows a
different format.

Thanks for taking the time to kick this around, Clif. I brute forced
my way through it, but your information is useful.

Keith

 

[Clif McIrvin]

For possible future use, as posters who use Google Groups will most
likely be completely unaware of the threading issues the new Google
Groups causes for newsreaders, I put the following boilerplate
together.

Posts from Google Groups seem to include this header:
Organization:http://groups.google.com

--
Notes on 25 May 2011

The "New Google Groups" UI does not thread correctly, but the old UI
still does.

From the old Google Groups UI there is a link to switch to the
"new Google Groups" but I am not aware of a link to get from the new
UI to the old UI.

I just spent a few minutes trying to learn how to get back to the old
google groups from the new groups .... and I didn't find a link to get
there from here.

The URL for the new UI looks something like
this:https://groups.google.com/d/forum/«groupname»
orhttps://groups.google.com/forum/#!forum/«groupname»

https://groups.google.com/group/«groupname»/topics

for
example:https://groups.google.com/group/microsoft.public.excel/topics

Using the 'group' (instead of the 'forum') form of the URL will take
you to the old UI, which as of May 2011 does thread correctly.

To be precise, the old UI includes the NNTP "References:" header, but
the new UI includes a "In-Reply-To:" header instead, which follows a
different format.

Thanks for taking the time to kick this around, Clif. I brute forced
my way through it, but your information is useful.

Keith

 

[GS]

Thanks, Clif! Knowing the problem lies in the new format is helpful in
taking away from the frustration of dealing with the incorrect
threading. Obviously, the new format doesn't care that users still use
NTTP readers. what you should do is post your info to a new thread with
an appropriate topic so it can be more easily found via search.

 

[Clif McIrvin]

Thanks, Clif! Knowing the problem lies in the new format is helpful in
taking away from the frustration of dealing with the incorrect
threading. Obviously, the new format doesn't care that users still use
NTTP readers. what you should do is post your info to a new thread
with an appropriate topic so it can be more easily found via search.

Good idea.

Do you have a reference to NNTP options that I could add at the bottom?
I have some information that I could assemble; but I know there are
other options.

NNTP Hosts:
e-s
aioe

Readers:
OE (requires XP or older)
T-bird

 

[GS]

Clif McIrvin used his keyboard to write :

Good idea.

Do you have a reference to NNTP options that I could add at the bottom? I
have some information that I could assemble; but I know there are other
options.

NNTP Hosts:
e-s
aioe

Readers:
OE (requires XP or older)
T-bird

I'd recommend T-Bird to anyone with Vista/Win7 or higher over using
Live Mail because of the number of reports of lost postings after M$
closed down the NTTP servers. Apparently, Live Mail caches get wiped
out.

I also think T-Bird is a better alternative than OE, but that's just my
opinion.

I don't know all the mirror servers that are available. I use e-s
because the folks in the classic VB forums discussed several
alternatives as to where to go. Fortunately, e-s also mirrors many of
the old M$ groups. You have to register for e-s but it's free and so no
big deal to set up an account. My was activated in less than an hour
but I know some others waited longer for this reason or that.

Otherwise, any 'good' newsreader is better than working an online
interface<IMO>!