Have data in Sheet1 appear in Sheet2?

[Ed from AZ]

I feel like such a brainless one for this! I'm trying get data on
Sheet1 to appear on Sheet2 if one cell on Sheet1 is marked with an
"X". So in Sheet2, E1, I've got:
=IF(D1="X",Sheet1!A16:C16=A1:C1,"")

I get a #VALUE error.

If I enter this an an array formula, I get FALSE.

I'm trying to stay away from macros and VBA on this one. Can someone
please drop-kick me in the right direction?

And while we're at it (since I can't seem to find my way out of _this_
paper bag!!), how do I use numbers from another cell as the row
references?
=IF(D1="X",Sheet1!A[ & G1]:C[ & G1]=A[ & H1]:C[ & H1],"")

*sigh*
Ed
(Some days I soar with the eagles,
and some days I'm just another goose!)

 

[JP]

The formula in Sheet2!E1 can't populate other cells, only itself.
You'll have to place the formula in each cell where you want the
values to appear, modifying the range as needed.

--JP

 

[Pete_UK]

Hi Ed,

I think you need something like this in A1 of Sheet2:

=IF(Sheet1!$D$1="X",Sheet1!A16,"")

Then copy it into B1 and C1 of Sheet2.

If you want to calculate the cell or range references, then you will
have to use the INDIRECT function, but I'm not exactly sure what you
are trying to achieve - can you give some more details?

Hope this helps.

Pete

 

[Ed from AZ]

The ultimate goal is this:
I can get the row number of the "X". And (thanks so much to Biff!!) I
can retrieve the row number of the first blank row in Sheet2. It's
just how to get the data from one sheet to another.

Ed

Hi Ed,

I think you need something like this in A1 of Sheet2:

=IF(Sheet1!$D$1="X",Sheet1!A16,"")

Then copy it into B1 and C1 of Sheet2.

If you want to calculate the cell or range references, then you will
have to use the INDIRECT function, but I'm not exactly sure what you
are trying to achieve - can you give some more details?

Hope this helps.

Pete

I feel like such a brainless one for this!  I'm trying get data on
Sheet1 to appear on Sheet2 if one cell on Sheet1 is marked with an
"X".  So in Sheet2, E1, I've got:
   =IF(D1="X",Sheet1!A16:C16=A1:C1,"")

I get a #VALUE error.

If I enter this an an array formula, I get FALSE.

I'm trying to stay away from macros and VBA on this one.  Can someone
please drop-kick me in the right direction?

And while we're at it (since I can't seem to find my way out of _this_
paper bag!!), how do I use numbers from another cell as the row
references?
   =IF(D1="X",Sheet1!A[ & G1]:C[ & G1]=A[ & H1]:C[ & H1],"")

*sigh*
Ed
(Some days I soar with the eagles,
and some days I'm just another goose!)- Hide quoted text -

- Show quoted text -

 

[Ed from AZ]

Oh carp!! Just thought of something.

Part of this whole thing revolves around returning the first empty row
from Sheet2 using a formula and placing information there. But as
soon as I place info there, the row number returned by the formula is
going to change because that row is no longer blank!

So unless there is also a way to "lock in" the result of a formula
without VBA, none of this will work!

Oh, well . . . back to the drawing board!

Ed

The ultimate goal is this:

I can get the row number of the "X".  And (thanks so much to Biff!!) I
can retrieve the row number of the first blank row in Sheet2.  It's
just how to get the data from one sheet to another.

Ed

Hi Ed,

I think you need something like this in A1 of Sheet2:

Then copy it into B1 and C1 of Sheet2.

If you want to calculate the cell or range references, then you will
have to use the INDIRECT function, but I'm not exactly sure what you
are trying to achieve - can you give some more details?

Hope this helps.

Pete

I feel like such a brainless one for this!  I'm trying get data on
Sheet1 to appear on Sheet2 if one cell on Sheet1 is marked with an
"X".  So in Sheet2, E1, I've got:
   =IF(D1="X",Sheet1!A16:C16=A1:C1,"")
I get a #VALUE error.
If I enter this an an array formula, I get FALSE.
I'm trying to stay away from macros and VBA on this one.  Can someone
please drop-kick me in the right direction?
And while we're at it (since I can't seem to find my way out of _this_
paper bag!!), how do I use numbers from another cell as the row
references?
   =IF(D1="X",Sheet1!A[ & G1]:C[ & G1]=A[ & H1]:C[ & H1],"")
*sigh*
Ed
(Some days I soar with the eagles,
and some days I'm just another goose!)- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -