Repost of VBA Code/Lookup question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve_n_KC - 18 Jun 2007 22:09 GMT

Sorry if this is showing twice, I could see my question last week but now I
can't find it anywhere???

I am trying to do this:
Value of A1 on sheet 1 will match the value of either D1,E1,F1...P1 of sheet 2
Assuming it matches E1, I want to copy E2:E52 of sheet 2 over to B1:B51 of
sheet 1

I don't think it matters but in case it does, the value that will be in A1
is actually {='sheet 3'!B3} and in my example above E1 would also be {='sheet
3'!B3}

So I should always have an exclusive, exact match but I can't get the
wording right. Can I even do this? If I have to I can replace my A1 and
D-P1's with text but I would rather not so that I can add duplicate sheets to
the Workbook (Values of D1 through P1 each has a ws of same name) with
minimal or no editing of the macro.

Please HELP!?!?

Signature

THANKS!

Steve

Reply to this Message

bj - 18 Jun 2007 22:24 GMT

Do you really want a macro to do it?
an equation such as
in B1 enter =offset('Sheet2"!$A$1,row(),match($A$1,'Sheet2'!$A$1:$P$1,0)-1)

> Sorry if this is showing twice, I could see my question last week but now I
> can't find it anywhere???
[quoted text clipped - 15 lines]
>
> Please HELP!?!?

Reply to this Message

Steve_n_KC - 21 Jun 2007 18:58 GMT

You guys are incredible!...You know better than I do what I want to do! =)
But I have a problem still.

I took the example code you gave me and adapted it to my "live" application
and...

Works GREAT in a blank workbook that I made with identical sheet names but
when I use it in my "live" application it returns #N/A with Value Not
Available Error as the highlighted message???? Any Ideas?

I need to copy this into 12 different sheets in 21 different workbooks-all
created from the same original so I will have same problem through out!

My formula:
=OFFSET('Skills Matrix'!$C$1,ROW('Skills Matrix'!C4:N4)-1,MATCH($B$5,'Skills
Matrix'!$C$1:$N$1,0)-1)

I've looked at Data Validation, Text orientation, Merged Cells, Cell
Referencing, Cell Formatting (all set to general) No Change in results???

Ideas?

Thanks Again for the direction toward a formula and away from coding it into
my macro...still need the macro but that will be much more "generic" now and
that was my original goal!

Signature

THANKS!

Steve

> Do you really want a macro to do it?
> an equation such as
[quoted text clipped - 19 lines]
> >
> > Please HELP!?!?

Reply to this Message

Steve_n_KC - 21 Jun 2007 20:51 GMT

Okay, after 3 days of fighting it I FIXED IT! =)

Now could someone tell me the WHAT and WHY to my HOW???

After cancelling out of it a couple hundred times, I finally paid attention
to a "Windows Navigation Box" that kept popping up every time that I would
change my formula. I can't get it to do it again but it looked just like the
box that opens for a File>Open, but it had a different message across the
top. Honestly I don't remember what it said but it was 2 words separated by
a ":"

I then used the box to map back to the same file that I was working on and
double clicked it. (Probably not the right thing to do since I STILL have no
idea what I was doing, but I thought I remembered reading something from this
forum about someone having to map a document back to itself)

After clicking the file, my document "blinked", the box went away and my
column of #N/A's changed to the x's and 0's that were expected to be returned
from formula!

Can SOMEONE please tell me:
1. What was wrong?
2. Is there a probable cause of how it GOT wrong?
3. Why did what I did fix it?
4. Did I fix all my workbooks that were made from the original I was working
on with that same mouse click?...I assumed that since all WB's came from same
original that all would have same problem so trying to repeat the message box
I tried to paste formulas into a different WB. Worked on first try? Would
it have worked all along or is this something I did?

If it matters, I'm hooked up to a network but all the files I've been
working with on this have been on my C drive.

THanks for any insight!,

Confused_n_KC

Signature

THANKS!

Steve

> You guys are incredible!...You know better than I do what I want to do! =)
> But I have a problem still.
[quoted text clipped - 45 lines]
> > >
> > > Please HELP!?!?

Reply to this Message

Harlan Grove - 22 Jun 2007 16:49 GMT

"Steve_n_KC" <spammers_should_B_shot@aol.com> wrote...
...

>After cancelling out of it a couple hundred times, I finally paid attention
>to a "Windows Navigation Box" that kept popping up every time that I would
[quoted text clipped - 4 lines]
>by
>a ":"

...

This happens when you copy formulas in workBOOK 1 with references to other
workSHEETS in workBOOK 1 into another workBOOK (workBOOK 2) that doesn't
have a workSHEET with the same name as the one in workBOOK 1. For example,
if you have a range containing several formulas like

=VLOOKUP(A!X99,A!$Y$99:$Z$1000,2)

in workBOOK 1 which contains a workSHEET named A, then you copy that range
into workBOOK 2 which lacks a worksheet named A, Excel will 'helpfully'
prompt you to change the invalid worksheet references to A to valid file
references, which is why it displays a file selection dialog for EACH
reference to A. That is, Excel interprets the A!X99 and similar tokens in
workBOOK 2 as EXTERNAL REFERENCES to a WORKBOOK named A. If you don't have
such a workBOOK (and it's very likely you don't), Excel believes it needs to
help you change the external references by making it easier for you to
select different FILES.

There may be no easy way to deal with this other than to create dummy
worksheet A in workbook 2 when you paste formulas into workbook 2, then use
Edit > Replace to change the worksheet names to the appropriate ones for
workbook 2 and delete the dummy worksheet A. You may believe you do already
have a worksheet A in workbook 2, but Excel is far less helpful at detecting
stray spaces, so worksheet 'A' in workbook 1 is not the same as either
worksheet 'A ' or ' A' in workbook 2.

Reply to this Message

Steve_n_KC - 23 Jun 2007 01:34 GMT

Hmmmmm...LOL

Thanks for the information...

Learn something (or about 20 somethings) everyday around here =)

Signature

THANKS!

Steve

> "Steve_n_KC" <spammers_should_B_shot@aol.com> wrote...
> ....
[quoted text clipped - 32 lines]
> stray spaces, so worksheet 'A' in workbook 1 is not the same as either
> worksheet 'A ' or ' A' in workbook 2.

Reply to this Message