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!?!?
--
THANKS!

Steve

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)

"Steve_n_KC" wrote:

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!?!?
--
THANKS!

Steve

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!
--
THANKS!

Steve


"bj" wrote:

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)

"Steve_n_KC" wrote:

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!?!?
--
THANKS!

Steve

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 FileOpen, 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
--
THANKS!

Steve


"Steve_n_KC" wrote:

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!
--
THANKS!

Steve


"bj" wrote:

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)

"Steve_n_KC" wrote:

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!?!?
--
THANKS!

Steve

"Steve_n_KC" 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
change my formula. I can't get it to do it again but it looked just like
the
box that opens for a FileOpen, 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 ":"
....

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.

Hmmmmm...LOL

Thanks for the information...

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

Steve


"Harlan Grove" wrote:

"Steve_n_KC" 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
change my formula. I can't get it to do it again but it looked just like
the
box that opens for a FileOpen, 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 ":"
....

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.