HELP! Return data if cell in row contains data

  • Thread starter Thread starter dee
  • Start date Start date
D

dee

Hi,

Does anyone know how to do this?

Example:

If F5 contains data, return the content of cell a1 to a
cell on another worksheet.

If statements don't work because need to look at
approximately 15 rows.

Thanks!
 
something like?

sub myssub()
for each c in [f1:f15]
x=sheets("sheet2").cells(65536,1).end(xlup).row+1
if c<>"" then c.offset(,-5).copy sheets("sheet2").cells(x,1)
next
end sub

I suspect you want more than this to specifics might help.
 
Be a bit more specific with ranges and what needs to happen, and we should be
able to help you out.
 
dee said:
Hi,

Does anyone know how to do this?

Example:

If F5 contains data, return the content of cell a1 to a
cell on another worksheet.

If statements don't work because need to look at
approximately 15 rows.

Thanks!
Click to expand...

Your question doesn't make sense. Where are the 15 rows you need to look at
in your example: "If F5 contains data, return the content of cell a1 to a
cell on another worksheet."

To return a result to "a cell on another worksheet", you write your formula
in that cell.
If you mean you want to return 15 results to 15 cells, you need 15 formulas.
You write the first in a suitable fashion, and copy down to the other 14.
If you mean you want to see if not just F5 but a range of 15 cells (say,
F1:F15) is blank, you can use
=IF(AND(ISBLANK(Sheet1!F1:F15)),Sheet1!A1,0)
Otherwise, please post back with a clearer explanation of what you do mean.
 
That would be great!

OK, here it is:

On sheet2, I have names of investments in column A.
These names should be returned to cells on sheet1 but
ONLY if the client invested money. They are standard
investment vehicle names that don't change.

So, a client may have $ invested in about 5 or 6 of these
out of about 15.

Sheet 2 is the data entry stuff and charts, etc. are
generated as figures are input. I have no problem with
that.

It's the part on sheet1 where we have the investment
vehicle breakdown we send to the client where I'm having
problems.

So - IF there is an amount in any cell from, for example,
d6 through j6 on sheet2, look at cell a6 and return that
value to sheet1, cell a55. Then repeat process for next
row on sheet2 and return value to cell a56 on sheet1, etc.

I know you can filter, then copy and paste values only,
etc., but the employee wants something easier.

HELP! Thanks!
 
OK, assuming your data is as follows:-

Names on Sheet2 in range A6:A105
Values ranging from 0 to anything in D6:J105 on Sheet2
Criteria, any cell in a row in D6:J105 on Sheet2 means that record is to be
returned to Sheet1

This solution requires 2 helper columns that can be hidden if you want:-

In cell L6 on Sheet2 put =COUNTIF(D6:J6,">0") and copy down to L105
In cell M6 on Sheet2 put =IF(L6=0,"",COUNTIF($L$6:$L6,">0")) and copy down to
M105

In Column L on Sheet2 you will see the number of cells in that row that contain
a value greater than 0
In Column M on Sheet2 you will see a running count of the rows that have a vlaue
greater than 0 anywhere in them (This can all be edited to say if the cell is
not blank - just not sure of your data)

On Sheet 1, assuming you want the data to be returned to cells B3:B102 if every
single record had a value, in cells A3:A102 put 1,2,3,4,5 etc (Fill Down)
Now select cells B3:B102 and then in the formula bar, paste the following
formula:-

=IF(ISERROR(INDEX(Sheet2!$A$6:$A$105,MATCH($A$3:$A$102,Sheet2!$M$6:$M$105,0))),"
",INDEX(Sheet2!$A$6:$A$105,MATCH($A$3:$A$102,Sheet2!$M$6:$M$105,0)))

Now hit CTRL+SHIFT+ENTER to array enter the data.

This should give you what you want, so now for some tidying up:-

Hide rows L:M on Sheet2

On Sheet1, Select cells A3:A102, do Format / Conditional formatting / Change
'cell Value is' to 'Formula is', then using the selector click on cell B3, and
you will now see =$B$3 appear. Hit F4 3 times till all the dollar signs
disappear and hit Enter and then type <>"", so that you now have =B3<>"" in the
dialog box. On the Font Tab in the dialog box, select White from the Colour
option, and then hit OK till you are out. You will now see a list of numbers
before each entry with no blanks. This will change automatically every time the
data changes.

I can also send you an example workbook if you like.
 
Oops - Slight Typo, but doesn't affect the formulas
Criteria, any cell in a row in D6:J105 on Sheet2 means that record is to be
returned to Sheet1
Click to expand...

Criteria, any cell in a row in D6:J105 on Sheet2 that has a value greater than 0
means that record from Col A is to be returned to Sheet1
 
thank fo ryour message.

Actually, sheet2 contains 15 rows that may or may not
contain values. If they do, the content of data on same
row in column A should returned to sheet1.

Here's the thing. The client will only have used maybe
(max) 8 of the 15 choices on sheet2. Usually only about
4. So, we won't just be returning the data from one
sheet to another 15 rows to 15 rows. We will only be
returning it if there are figures somewhere on the row.
For example, there may be figures on sheet2 on rows 6, 9,
15 and 20. That means the others should be ignored.

Does this make sense? THANKS
 
Hi again,

Here is the formula I pasted on Sheet1. What you
referred to as Sheet2 is called Data Entry. The cells
whose content I want returned to Sheet1 are Data Entry
A4:A18.

I pasted the formulas on the Data Entry sheet in columns
K and L and they work fine.

I then put 1 through 8 on Sheet1, from A35:A42. This is
because even though looking up bigger range on Data
Entry, any one client will have a maximum of 8 rows from
the range on DAta Entry that will contain values.

I tried pasting the formula below in cells B35:B42 as an
array, but it just gives me blanks.

I think it's close, but can't seem to figure out where
I'm going wrong/ Must I name cells or something?

Thank you.
=IF(ISERROR(INDEX(Data Entry!$A$4:$A$18,MATCH
($A$35:$A$42,Data Entry!$L$4:$L$18,0)))," ",INDEX(Data
Entry!$A$4:$A$18,MATCH($A$35:$A$42,Data Entry!
$L$4:$L$18,0)))
-----Original Message-----
Oops - Slight Typo, but doesn't affect the formulas


Criteria, any cell in a row in D6:J105 on Sheet2 that has a value greater than 0
means that record from Col A is to be returned to Sheet1

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

--------------------------------------------------------- -------------------
Attitude - A little thing that makes a BIG difference
--------------------------------------------------------- -------------------
Click to expand...
($L$6:$L6,">0")) and copy down
 
Hi again,

Here is the formula I pasted on Sheet1. What you
referred to as Sheet2 is called Data Entry. The cells
whose content I want returned to Sheet1 are Data Entry
A4:A18.

I pasted the formulas on the Data Entry sheet in columns
K and L and they work fine.

I then put 1 through 8 on Sheet1, from A35:A42. This is
because even though looking up bigger range on Data
Entry, any one client will have a maximum of 8 rows from
the range on DAta Entry that will contain values.

I tried pasting the formula below in cells B35:B42 as an
array, but it just gives me blanks.

I think it's close, but can't seem to figure out where
I'm going wrong/ Must I name cells or something?

Thank you.
=IF(ISERROR(INDEX(Data Entry!$A$4:$A$18,MATCH
($A$35:$A$42,Data Entry!$L$4:$L$18,0)))," ",INDEX(Data
Entry!$A$4:$A$18,MATCH($A$35:$A$42,Data Entry!
$L$4:$L$18,0)))
-----Original Message-----
Oops - Slight Typo, but doesn't affect the formulas


Criteria, any cell in a row in D6:J105 on Sheet2 that has a value greater than 0
means that record from Col A is to be returned to Sheet1

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

--------------------------------------------------------- -------------------
Attitude - A little thing that makes a BIG difference
--------------------------------------------------------- -------------------
Click to expand...
($L$6:$L6,">0")) and copy down
 
It's bombing because you don't have single quotes around your sheet name, which
you need if your sheet name has a space in it. Try the following:-

=IF(ISERROR(INDEX('Data Entry'!$A$4:$A$18,MATCH($A$35:$A$134,'Data
Entry'!$L$4:$L$18,0))),"",INDEX('Data Entry'!$A$4:$A$18,MATCH($A$35:$A$134,'Data
Entry'!$L$4:$L$18,0)))
 
Here is what I pasted and unfortunately it still doesn't
work. Is it because the number of rows on Data Entry
exceed the number of rows that I am returning values to
on Sheet 1?

=IF(ISERROR(INDEX('Data Entry'!$A$4:$A$18,MATCH
($A$35:$A$42,'Data
Entry'!$L$4:$L$18,0))),"",INDEX('Data Entry'!
$A$4:$A$18,MATCH($A$35:$A$42,'Data
Entry'!$L$4:$L$18,0)))

Thanks so much for all of your help!
-----Original Message-----
It's bombing because you don't have single quotes around your sheet name, which
you need if your sheet name has a space in it. Try the following:-

=IF(ISERROR(INDEX('Data Entry'!$A$4:$A$18,MATCH ($A$35:$A$134,'Data
Entry'!$L$4:$L$18,0))),"",INDEX('Data Entry'! $A$4:$A$18,MATCH($A$35:$A$134,'Data
Entry'!$L$4:$L$18,0)))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------- -------------------
Attitude - A little thing that makes a BIG difference
--------------------------------------------------------- -------------------



Hi again,

Here is the formula I pasted on Sheet1. What you
referred to as Sheet2 is called Data Entry. The cells
whose content I want returned to Sheet1 are Data Entry
A4:A18.

I pasted the formulas on the Data Entry sheet in columns
K and L and they work fine.

I then put 1 through 8 on Sheet1, from A35:A42. This is
because even though looking up bigger range on Data
Entry, any one client will have a maximum of 8 rows from
the range on DAta Entry that will contain values.

I tried pasting the formula below in cells B35:B42 as an
array, but it just gives me blanks.

I think it's close, but can't seem to figure out where
I'm going wrong/ Must I name cells or something?

Thank you.
=IF(ISERROR(INDEX(Data Entry!$A$4:$A$18,MATCH
($A$35:$A$42,Data Entry!$L$4:$L$18,0)))," ",INDEX(Data
Entry!$A$4:$A$18,MATCH($A$35:$A$42,Data Entry!
$L$4:$L$18,0)))
-----Original Message-----
Oops - Slight Typo, but doesn't affect the formulas

Criteria, any cell in a row in D6:J105 on Sheet2
Click to expand...
means
that record is to be
returned to Sheet1

Criteria, any cell in a row in D6:J105 on Sheet2 that has a value greater than 0
means that record from Col A is to be returned to Sheet1
Click to expand...
MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP

------------------------------------------------------
Click to expand...
---
-------------------
Attitude - A little thing that makes a BIG difference
------------------------------------------------------
Click to expand...
---
-------------------
OK, assuming your data is as follows:-

Names on Sheet2 in range A6:A105
Values ranging from 0 to anything in D6:J105 on Sheet2
Criteria, any cell in a row in D6:J105 on Sheet2
Click to expand...
means
that record is to be
returned to Sheet1

This solution requires 2 helper columns that can be hidden if you want:-

In cell L6 on Sheet2 put =COUNTIF(D6:J6,">0")
Click to expand...
and
copy down to L105
In cell M6 on Sheet2 put =IF(L6=0,"",COUNTIF
Click to expand...
($L$6:$L6,">0")) and copy down
to
M105

In Column L on Sheet2 you will see the number of cells in that row that
contain
a value greater than 0
In Column M on Sheet2 you will see a running count
Click to expand...
of
the rows that have a
vlaue
greater than 0 anywhere in them (This can all be edited to say if the cell is
not blank - just not sure of your data)

On Sheet 1, assuming you want the data to be
Click to expand...
returned
to cells B3:B102 if
every
single record had a value, in cells A3:A102 put 1,2,3,4,5 etc (Fill Down)
Now select cells B3:B102 and then in the formula
Click to expand...
bar,
paste the following
formula:-


=IF(ISERROR(INDEX(Sheet2!$A$6:$A$105,MATCH ($A$3:$A$102,Sheet2!$M$6:$M$105,0))),"
$M$6:$M$105,0)))

Now hit CTRL+SHIFT+ENTER to array enter the data.

This should give you what you want, so now for some tidying up:-

Hide rows L:M on Sheet2

On Sheet1, Select cells A3:A102, do Format /
Click to expand...
Conditional formatting / Change
'cell Value is' to 'Formula is', then using the selector click on cell B3, and
you will now see =$B$3 appear. Hit F4 3 times till all the dollar signs
disappear and hit Enter and then type <>"", so that
Click to expand...
you now have =B3 said:
the
dialog box. On the Font Tab in the dialog box,
Click to expand...
select
White from the Colour
option, and then hit OK till you are out. You will now see a list of numbers
before each entry with no blanks. This will change automatically every time
the
data changes.

I can also send you an example workbook if you like.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------
Click to expand...
---
---------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------
Click to expand...
---
---------------------
That would be great!

OK, here it is:

On sheet2, I have names of investments in column A.
These names should be returned to cells on sheet1 but
ONLY if the client invested money. They are standard
investment vehicle names that don't change.

So, a client may have $ invested in about 5 or 6
Click to expand...
of
these
out of about 15.

Sheet 2 is the data entry stuff and charts, etc. are
generated as figures are input. I have no problem with
that.

It's the part on sheet1 where we have the investment
vehicle breakdown we send to the client where I'm having
problems.

So - IF there is an amount in any cell from, for example,
d6 through j6 on sheet2, look at cell a6 and
Click to expand...
return
that
value to sheet1, cell a55. Then repeat process
Click to expand...
for
next
row on sheet2 and return value to cell a56 on sheet1, etc.

I know you can filter, then copy and paste values only,
etc., but the employee wants something easier.

HELP! Thanks!
-----Original Message-----
Be a bit more specific with ranges and what needs to
happen, and we should be
able to help you out.
Click to expand...
Microsoft
MVP -
Excel
Sys Spec - Win XP Pro / XL2K
Click to expand...
&
XLXP
-------------------------------------------------
Click to expand...
---
----- ---
-----
-------------------



message
Hi,

Does anyone know how to do this?

Example:

If F5 contains data, return the content of cell a1 to a
cell on another worksheet.

If statements don't work because need to look at
approximately 15 rows.

Thanks!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date:
01/11/2003


.
Click to expand...
Date:
01/11/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003


.
Click to expand...
Click to expand...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.537 / Virus Database: 332 - Release Date: 06/11/2003


.
Click to expand...
 
Kinda hard without seeing the data - Are you able to send me a copy or a
desensitised copy that I could try first hand. You would need to take the
nospam bit out of my email. I can then try and fix it and return it for you.
 
Back
Top