How can I do this ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Just Me - 12 Nov 2006 03:10 GMT

I have a speadsheet that has 7 worksheets, each one is a month of my credit
card statements, is it possible to make a 8th worksheet and have it search
the other 7 and find any cells that contains the word "wal-mart" in them and
have them all appear in the 8th worksheet. In other words I want only the
wal-mart data to be on the 8th worksheet.

Thanks in advance

Reply to this Message

Bob Phillips - 12 Nov 2006 10:03 GMT

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:7"))&"!A1:A1000"),"wal-ma
rt",
INDIRECT("Sheet"&ROW(INDIRECT("1:7"))&"!B1:B1000")))

where the string is being looked for in A1:A1000, amounts in B1:B100, over
sheets Sheet1:Sheet7

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I have a speadsheet that has 7 worksheets, each one is a month of my credit
> card statements, is it possible to make a 8th worksheet and have it search
[quoted text clipped - 3 lines]
>
> Thanks in advance

Reply to this Message

Just Me - 12 Nov 2006 10:38 GMT

> =SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:7"))&"!A1:A1000"),"wal-ma
> rt",
[quoted text clipped - 13 lines]
>>
>> Thanks in advance

I appreciate your help but I don't really know Excel that well so I am not
sure what to do with the formula you gave me. Could you please tell me
where that formula should go and do I have to add anything to it?

I don't mean to sound dumb but I don't know Excell well at all

Reply to this Message

Bob Phillips - 12 Nov 2006 10:46 GMT

Check the conditions I mentioned, change if required, and input it on the
8th sheet and see what happens.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:7"))&"!A1:A1000"),"wal-ma

> > rt",
> > INDIRECT("Sheet"&ROW(INDIRECT("1:7"))&"!B1:B1000")))
[quoted text clipped - 18 lines]
>
> I don't mean to sound dumb but I don't know Excell well at all

Reply to this Message

Otto Moehrbach - 13 Nov 2006 22:37 GMT

Post back and tell me how many columns you want copied from the other 7
sheets and pasted to the 8th sheet. What is the starting row of your data
in the 7 sheets. What is the starting row where you want the data pasted in
the 8th sheet. What column of the 7 sheets holds the key word (Wal-Mart in
your example). HTH Otto

>I have a speadsheet that has 7 worksheets, each one is a month of my credit
>card statements, is it possible to make a 8th worksheet and have it search
[quoted text clipped - 3 lines]
>
> Thanks in advance

Reply to this Message