I have a column of eleven non-sequent cells that may or may not contain a
currency value.

I need to copy this information across to another column of sequent cells,
but I only wish to copy the cells that contain a value. Cells that are blank
(empty) or showing £zero should be ignored.

For example:

non-sequent data source cells:

C1 £50.00
C3 £100.00
C5 £0.00
C7 £10.00
C9 'Blank'
C11 'Blank'
C14 £60.00
C16 £30.00
C18 'Blank'
C20 'Blank'
C22 'Blank'


Result:

A1 £50.00 (from C1)
A2 £100.00 (from C3)
A3 £10.00 (from C7; C5 ignored)
A4 £60.00 (from C14: C9 & C11 ignored)
A5 £30.00 (from C16)
A6 'Blank'
A7 'Blank'
A8 'Blank'
A9 'Blank'
A10 'Blank'
A11 'Blank'

Can this be achieved without resorting to a macro?

Does anyone have the time to point me in the right direction?

Thanks
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Hi mlv,

Select all of your data in column C
Tap F5
Click Special
Check Constants and OK
Copy and paste the selected data to A1

(Depending on what is in the blank cells you may
need to uncheck Text, Logicals and Errors
after checking Constants)

HTH
Martin

"mlv" <(E-Mail Removed)> wrote in message
news:fdimhg$scu$(E-Mail Removed)...
>I have a column of eleven non-sequent cells that may or may not contain a
>currency value.
>
> I need to copy this information across to another column of sequent cells,
> but I only wish to copy the cells that contain a value. Cells that are
> blank (empty) or showing £zero should be ignored.
>
> For example:
>
> non-sequent data source cells:
>
> C1 £50.00
> C3 £100.00
> C5 £0.00
> C7 £10.00
> C9 'Blank'
> C11 'Blank'
> C14 £60.00
> C16 £30.00
> C18 'Blank'
> C20 'Blank'
> C22 'Blank'
>
>
> Result:
>
> A1 £50.00 (from C1)
> A2 £100.00 (from C3)
> A3 £10.00 (from C7; C5 ignored)
> A4 £60.00 (from C14: C9 & C11 ignored)
> A5 £30.00 (from C16)
> A6 'Blank'
> A7 'Blank'
> A8 'Blank'
> A9 'Blank'
> A10 'Blank'
> A11 'Blank'
>
> Can this be achieved without resorting to a macro?
>
> Does anyone have the time to point me in the right direction?
>
> Thanks
> --
> Mike
> -Please remove 'safetycatch' from email address before firing off your
> reply-
>

Martin W wrote:
>
> Select all of your data in column C
> Tap F5
> Click Special
> Check Constants and OK
> Copy and paste the selected data to A1
>
> (Depending on what is in the blank cells you may
> need to uncheck Text, Logicals and Errors
> after checking Constants)

Hi Martin, thanks for the reply.

On re-reading my original post, I realise I didn't make my requirements very
clear.

I would like a formula that automatically copies all of the valid data from
the eleven designated C cells across into the A cell column and that will
auto-update the A cell column entries if any C cell data subsequently
changes.

Basically, all the valid data from the eleven designated C cells (which
could be anything from no valid entries to eleven valid entries) should be
copied across into the A cell column range, starting at cell A1 and using as
many A cells as necessary, e.g. If cells C1, C3 and C14 contain valid
(non-zero, non-blank) entries, then these three entries should be
automatically copied in the same order into cells A1, A2 and A3. If a valid
entry is subsequently put into cell C9, then the new entry in cell C9 should
be copied to cell A3 and the existing entry in cell A3 (copy of cell C14
data) should now appear in cell A4. If all eleven designated C cells have
valid entries, then the data should be copied in the same order into cells
A1 to A11.

Hmm, it's starting to sound a bit complicated. Maybe I should continue with
manually copying the valid data across?
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Hi mlv,

Try this,
Insert a helper column C (so your values are now in D)

Put this in C1 and drag down to C22
=IF(OR(D1="",D1=0),"",COUNT(INDIRECT("D1"&ROW())))

Put this in A1 and drag down to A11
=IF(COUNT($C$1:$C$22)<ROW(),"",VLOOKUP(SMALL($C$1:$C$22,ROW()),$C$1:$D$22,2))

If needed hide column C.

HTH
Martin


"mlv" <(E-Mail Removed)> wrote in message
news:fdj78a$40g$(E-Mail Removed)...
> Martin W wrote:
>>
>> Select all of your data in column C
>> Tap F5
>> Click Special
>> Check Constants and OK
>> Copy and paste the selected data to A1
>>
>> (Depending on what is in the blank cells you may
>> need to uncheck Text, Logicals and Errors
>> after checking Constants)
>
> Hi Martin, thanks for the reply.
>
> On re-reading my original post, I realise I didn't make my requirements
> very clear.
>
> I would like a formula that automatically copies all of the valid data
> from the eleven designated C cells across into the A cell column and that
> will auto-update the A cell column entries if any C cell data subsequently
> changes.
>
> Basically, all the valid data from the eleven designated C cells (which
> could be anything from no valid entries to eleven valid entries) should be
> copied across into the A cell column range, starting at cell A1 and using
> as many A cells as necessary, e.g. If cells C1, C3 and C14 contain valid
> (non-zero, non-blank) entries, then these three entries should be
> automatically copied in the same order into cells A1, A2 and A3. If a
> valid entry is subsequently put into cell C9, then the new entry in cell
> C9 should be copied to cell A3 and the existing entry in cell A3 (copy of
> cell C14 data) should now appear in cell A4. If all eleven designated C
> cells have valid entries, then the data should be copied in the same order
> into cells A1 to A11.
>
> Hmm, it's starting to sound a bit complicated. Maybe I should continue
> with manually copying the valid data across?
> --
> Mike
> -Please remove 'safetycatch' from email address before firing off your
> reply-
>

Just had another thought after posting,
If there are text values inbetween your data
then you may need to add ISTEXT to the OR function like this
=IF(OR(D2="",D2=0,ISTEXT(D2)),"",COUNT(INDIRECT("D1"&ROW())))

HTH
Martin


"MartinW" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi mlv,
>
> Try this,
> Insert a helper column C (so your values are now in D)
>
> Put this in C1 and drag down to C22
> =IF(OR(D1="",D1=0),"",COUNT(INDIRECT("D1"&ROW())))
>
> Put this in A1 and drag down to A11
> =IF(COUNT($C$1:$C$22)<ROW(),"",VLOOKUP(SMALL($C$1:$C$22,ROW()),$C$1:$D$22,2))
>
> If needed hide column C.
>
> HTH
> Martin
>
>
> "mlv" <(E-Mail Removed)> wrote in message
> news:fdj78a$40g$(E-Mail Removed)...
>> Martin W wrote:
>>>
>>> Select all of your data in column C
>>> Tap F5
>>> Click Special
>>> Check Constants and OK
>>> Copy and paste the selected data to A1
>>>
>>> (Depending on what is in the blank cells you may
>>> need to uncheck Text, Logicals and Errors
>>> after checking Constants)
>>
>> Hi Martin, thanks for the reply.
>>
>> On re-reading my original post, I realise I didn't make my requirements
>> very clear.
>>
>> I would like a formula that automatically copies all of the valid data
>> from the eleven designated C cells across into the A cell column and that
>> will auto-update the A cell column entries if any C cell data
>> subsequently changes.
>>
>> Basically, all the valid data from the eleven designated C cells (which
>> could be anything from no valid entries to eleven valid entries) should
>> be copied across into the A cell column range, starting at cell A1 and
>> using as many A cells as necessary, e.g. If cells C1, C3 and C14 contain
>> valid (non-zero, non-blank) entries, then these three entries should be
>> automatically copied in the same order into cells A1, A2 and A3. If a
>> valid entry is subsequently put into cell C9, then the new entry in cell
>> C9 should be copied to cell A3 and the existing entry in cell A3 (copy of
>> cell C14 data) should now appear in cell A4. If all eleven designated C
>> cells have valid entries, then the data should be copied in the same
>> order into cells A1 to A11.
>>
>> Hmm, it's starting to sound a bit complicated. Maybe I should continue
>> with manually copying the valid data across?
>> --
>> Mike
>> -Please remove 'safetycatch' from email address before firing off your
>> reply-
>>
>
>

Must be time for bed, change that last correction to

=IF(ISNUMBER(D1),COUNT(INDIRECT("D1"&ROW())),"")

HTH
Martin

"MartinW" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Just had another thought after posting,
> If there are text values inbetween your data
> then you may need to add ISTEXT to the OR function like this
> =IF(OR(D2="",D2=0,ISTEXT(D2)),"",COUNT(INDIRECT("D1"&ROW())))
>
> HTH
> Martin
>
>
> "MartinW" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi mlv,
>>
>> Try this,
>> Insert a helper column C (so your values are now in D)
>>
>> Put this in C1 and drag down to C22
>> =IF(OR(D1="",D1=0),"",COUNT(INDIRECT("D1"&ROW())))
>>
>> Put this in A1 and drag down to A11
>> =IF(COUNT($C$1:$C$22)<ROW(),"",VLOOKUP(SMALL($C$1:$C$22,ROW()),$C$1:$D$22,2))
>>
>> If needed hide column C.
>>
>> HTH
>> Martin
>>
>>
>> "mlv" <(E-Mail Removed)> wrote in message
>> news:fdj78a$40g$(E-Mail Removed)...
>>> Martin W wrote:
>>>>
>>>> Select all of your data in column C
>>>> Tap F5
>>>> Click Special
>>>> Check Constants and OK
>>>> Copy and paste the selected data to A1
>>>>
>>>> (Depending on what is in the blank cells you may
>>>> need to uncheck Text, Logicals and Errors
>>>> after checking Constants)
>>>
>>> Hi Martin, thanks for the reply.
>>>
>>> On re-reading my original post, I realise I didn't make my requirements
>>> very clear.
>>>
>>> I would like a formula that automatically copies all of the valid data
>>> from the eleven designated C cells across into the A cell column and
>>> that will auto-update the A cell column entries if any C cell data
>>> subsequently changes.
>>>
>>> Basically, all the valid data from the eleven designated C cells (which
>>> could be anything from no valid entries to eleven valid entries) should
>>> be copied across into the A cell column range, starting at cell A1 and
>>> using as many A cells as necessary, e.g. If cells C1, C3 and C14 contain
>>> valid (non-zero, non-blank) entries, then these three entries should be
>>> automatically copied in the same order into cells A1, A2 and A3. If a
>>> valid entry is subsequently put into cell C9, then the new entry in cell
>>> C9 should be copied to cell A3 and the existing entry in cell A3 (copy
>>> of cell C14 data) should now appear in cell A4. If all eleven designated
>>> C cells have valid entries, then the data should be copied in the same
>>> order into cells A1 to A11.
>>>
>>> Hmm, it's starting to sound a bit complicated. Maybe I should continue
>>> with manually copying the valid data across?
>>> --
>>> Mike
>>> -Please remove 'safetycatch' from email address before firing off your
>>> reply-
>>>
>>
>>
>
>

Martin wrote:
>
> Must be time for bed, change that last correction to
>
> =IF(ISNUMBER(D1),COUNT(INDIRECT("D1"&ROW())),"")
>
> HTH
> Martin
>
>> Martin wrote:
>> Just had another thought after posting,
>> If there are text values inbetween your data
>> then you may need to add ISTEXT to the OR function like this
>> =IF(OR(D2="",D2=0,ISTEXT(D2)),"",COUNT(INDIRECT("D1"&ROW())))
>>
>> HTH
>> Martin
>>
>>> Martin wrote:
>>> Hi mlv,
>>>
>>> Try this,
>>> Insert a helper column C (so your values are now in D)
>>>
>>> Put this in C1 and drag down to C22
>>> =IF(OR(D1="",D1=0),"",COUNT(INDIRECT("D1"&ROW())))
>>>
>>> Put this in A1 and drag down to A11
>>> =IF(COUNT($C$1:$C$22)<ROW(),"",VLOOKUP(SMALL($C$1:$C$22,ROW()),$C$1:$D$22,2))
>>>
>>> If needed hide column C.
>>>
>>> HTH
>>> Martin

Thanks for all your help Martin, I'll try the formula out tonight.

(BTW, I can only access this newsgroup at work - I can't find it on the
Tiscali news server that I use at home).
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Hi Mike,

As you appear to be using Outlook Express at work I am puzzled why you are
not using it at home, always assuming that you are using Windows.

Dave MdRitchie has some instructions here:

http://www.mvps.org/dmcritchie/ie/oe6nws01.htm


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"mlv" <(E-Mail Removed)> wrote in message
news:fdrbdc$bnt$(E-Mail Removed)...
> Martin wrote:
>>
>> Must be time for bed, change that last correction to
>>
>> =IF(ISNUMBER(D1),COUNT(INDIRECT("D1"&ROW())),"")
>>
>> HTH
>> Martin
>>
>>> Martin wrote:
>>> Just had another thought after posting,
>>> If there are text values inbetween your data
>>> then you may need to add ISTEXT to the OR function like this
>>> =IF(OR(D2="",D2=0,ISTEXT(D2)),"",COUNT(INDIRECT("D1"&ROW())))
>>>
>>> HTH
>>> Martin
>>>
>>>> Martin wrote:
>>>> Hi mlv,
>>>>
>>>> Try this,
>>>> Insert a helper column C (so your values are now in D)
>>>>
>>>> Put this in C1 and drag down to C22
>>>> =IF(OR(D1="",D1=0),"",COUNT(INDIRECT("D1"&ROW())))
>>>>
>>>> Put this in A1 and drag down to A11
>>>> =IF(COUNT($C$1:$C$22)<ROW(),"",VLOOKUP(SMALL($C$1:$C$22,ROW()),$C$1:$D$22,2))
>>>>
>>>> If needed hide column C.
>>>>
>>>> HTH
>>>> Martin
>
> Thanks for all your help Martin, I'll try the formula out tonight.
>
> (BTW, I can only access this newsgroup at work - I can't find it on the
> Tiscali news server that I use at home).
> --
> Mike
> -Please remove 'safetycatch' from email address before firing off your
> reply-
>
>
>

Hi mlv,

Best to ignore that last formula I posted. ISNUMBER doesn't ignore
the zero values. I think the first correction with ISTEXT should be
the way to go.

HTH
Martin


"mlv" <(E-Mail Removed)> wrote in message
news:fdrbdc$bnt$(E-Mail Removed)...
> Martin wrote:
>>
>> Must be time for bed, change that last correction to
>>
>> =IF(ISNUMBER(D1),COUNT(INDIRECT("D1"&ROW())),"")
>>
>> HTH
>> Martin
>>
>>> Martin wrote:
>>> Just had another thought after posting,
>>> If there are text values inbetween your data
>>> then you may need to add ISTEXT to the OR function like this
>>> =IF(OR(D2="",D2=0,ISTEXT(D2)),"",COUNT(INDIRECT("D1"&ROW())))
>>>
>>> HTH
>>> Martin
>>>
>>>> Martin wrote:
>>>> Hi mlv,
>>>>
>>>> Try this,
>>>> Insert a helper column C (so your values are now in D)
>>>>
>>>> Put this in C1 and drag down to C22
>>>> =IF(OR(D1="",D1=0),"",COUNT(INDIRECT("D1"&ROW())))
>>>>
>>>> Put this in A1 and drag down to A11
>>>> =IF(COUNT($C$1:$C$22)<ROW(),"",VLOOKUP(SMALL($C$1:$C$22,ROW()),$C$1:$D$22,2))
>>>>
>>>> If needed hide column C.
>>>>
>>>> HTH
>>>> Martin
>
> Thanks for all your help Martin, I'll try the formula out tonight.
>
> (BTW, I can only access this newsgroup at work - I can't find it on the
> Tiscali news server that I use at home).
> --
> Mike
> -Please remove 'safetycatch' from email address before firing off your
> reply-
>
>

Sandy Mann wrote:
>
> Hi Mike,
>
> As you appear to be using Outlook Express at work I am puzzled
> why you are not using it at home, always assuming that you are
> using Windows.
>
> Dave MdRitchie has some instructions here:
>
> http://www.mvps.org/dmcritchie/ie/oe6nws01.htm

Hi Sandy

I am using Windows XP SP2 and Outlook Express at home.

I have recently subscribed to Tiscali Max Broadband and I connect to the
Tiscali news server. There are quite a few Microsoft Excel ngs on the
Tiscali server, but not <microsoft.public.excel>. The nearest is
<microsoft.public.excel.misc>.

I emailed Tiscali Technical Help somewhere on a distant planet to ask about
the missing ng, but quite honestly I would have got a more comprehensible
response if I had emailed my neighbour's goldfish.

I guess I need to set up another connection directly to the Microsoft
newsservers.

Thanks for the link.

Regards
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-