Glad you're (kind of) happy.
If you get time, you may want to revisit the =pull() function to see if you can
get it to work.
CLR wrote:
Cool, Dave........I believe you've hit upon the answer. What I have is a
"Proficiency Matrix" that simply stated, lists names down the left column
and tasks across the top. Then, each employee has a file that lists those
items they've been trained to and the level. The formulas I need are to
concatenate each name with a task and look that up in the concatenated
name-employeenumber file to return their proficiency level at that point on
the matrix. In operation, the sysmgr selects a matrix, then edits the names
and tasks as desired, and so then it would seem ok to push the "Update"
button to make things happen. I don't know how to go about all that right
off the top of my head, but it all seems within the realm of possibility
for me being able to muddle through it.
Many many thanks Dave, you've been a lifesaver, and I do appreciate you
hanging in there with me.................
Vaya con Dios,
Chuck, CABGx3
"Dave Peterson" wrote in message
...
WinXP Home/xl2003 works.
=======
One more option (that I can think of).
You use a macro to build the formulas--no =indirect() allowed.
You'll just create formulas like:
='C:\[PullTestWork.xls]Sheet1'!$A$1
And populate your range of cells that way.
I'm not sure how your data is laid out or what you have to do, but maybe
you
could put a button on the worksheet that says: Retrieve Values Now.
Then the macro assigned to that button builds those formulas.
CLR wrote:
Ok, I'm back at work on this approx 800Mhz Win98SE (4.10.2222A) machine
with
Office97 and 512MB RAM. I created two new books just like I did at
home,
installed Harlan's code and Dave's 97Function and corrected the InStrRev
lines..... and all I get is #REF! with the external book closed, using
this
formula
=pull("'C:\[PullTestWork.xls]Sheet1'!$A$1")
and it works ok with the external book open......the same response I got
at
home on my desktop machine.
This formula in another cell returns the correct value, so that
demonstrates
the file exists and no type-o's.
='C:\[PullTestWork.xls]Sheet1'!$A$1
So, the senario is:
At-work Win98Se machine, XL97................it don't work
At-home WinMe machine XL 97 & 2k...........it don't work
At-home WinMe machine XL97 no Add-ins...it don't work
El Cheapo laptop Win98SE XL97..................it works
Dave's Unknown OS XL2003........................it works
To re-iterate my original goal, I would like to have a worksheet with
about
300 formulas that will take a CONCATENATED value and look it up in a
range in
a closed workbook called from a CONCATENATED filename.....something
like:
=VLOOKUP(C8&D8,C:\[A8&B8.XLS]Sheet1!$A$10:$B$100,2,FALSE)
To this point, the offered suggestions have been the INDIRECT formula
which
only works with open external files, and the PULL UDF which is supposed
to do
this, but has only seen limited success described above...........I
would
love to use it, if it would be consistant between machines.
Any other ideas or suggestions, please?
Vaya con Dios,
Chuck, CABGx3
"CLR" wrote:
Hi Dave..........
Ok, I did everything you said, except the part about closing the
Add-ins
didn't work too good, I got some kind of error message, so I just did
Tools
Add-ins and un-checked them all.........then did all the other
stuff and
still only get the #REF! in B1...........then I moved both the new
files
over to the laptop, and they worked there fine again..............so,
I
will check again tomorrow on the work computer and see how that
goes.........
Thanks again,
Vaya con Dios,
Chuck, CABGx3
"Dave Peterson" wrote in message
...
Well, this is a horse of a different color! (Or some statement of
surprise!)
Just some guesses...
If you start with a brand new workbook on the troublesome pc--close
all
addins
and all other workbooks (just in case there's a name conflict
(instrrev97() or
pull()).
And put the code in that new workbook.
Then create a new workbook with something in A1 of sheet1.
Back to the workbook with the code.
type = in A1 of sheet1
point at sheet1 a1 of that other workbook
swap to the other workbook and close/save it.
Then put the equivalent of this in B1:
=pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")
Ps. If you're gonna share the code with users of xl97, I'd just
change
the code
to always use instrrev97--but you could code around it if you really
wanted.
pps. If you have addins loaded, you can close them by going into the
VBE
and
hitting ctrl-g.
Then typing:
workbooks("myaddin.xla").close
CLR wrote:
Yeah, I hear ya RD.........I like it that way too.
Now, I got a really weird thing to report.
Last weekend I bought a 366 Celeron laptop for $15 at a yard sale.
It
has
Win98SE on it with Office97 and only 32MB RAM. I just now copied
the
PullMaster.xls and PullTest.xls I have been using here on my
1.2GHz AMD
Desktop with WinMe and Office97 and 2k and 512MB RAM (with which
nothing
worked), over to the laptop. Once on the laptop and adjusting the
InStrRev
lines per Dave's 97function, the durn thing worked
perfectly!!!!!........go
figure.
So, this puts me in a quandry. I must go back to work tomorrow
and try
on
that machine again, (Win98SE, Office97, 512MB RAM) the same
protocol
I've
used here, thinking maybe I screwed something up Friday. If it
works,
fine,
the problem boils down to my machine here at home and I can live
with
that,
worry about it later. But if it dont work at work tomorrow, I
have no
idea
to say why it works on some machines and not on
others.........strange.......could it possibly be a "setting"
somewhere?...........or a rights restriction?
Vaya con Dios,
Chuck, CABGx3
<<snipped
--
Dave Peterson
--
Dave Peterson
|