Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Blaknight
 
Posts: n/a
Default This will test you!!


Hello All,

My first post here as i am very stuck with a very complex formula.

-=See attachment=-

Please if you can help grab the attachment so you know what i am on
about.

This is my problem. I have 6 columns of data labeled
- Item, Date, Qty, Quality, Action, Charater.

What i want is the formula to search A11 to F41 (F41 will be greater
with more data entered) for a word in column A say *Medium Leather*
then look for the value in column C of that word found in column A and
continue.

I have achieved this but when it has found the 1st word it doesn't
continue on to find another word of the same. This is what i have
used.

=VLOOKUP("Medium Leather",A11:F41,3,)

Then on top of that i want it to look in column E for one of two words.
Deposit and Withdrawn. I want the formula to add the data found in
column C when the word Deposit has been found in column E, and
subtracted if the word Withdrawn has been found.

This infomation needs to be placed in the Qty column in the *Totals
Table* next to the corresponding Item.

So. basically i want

- Search Entire *Data Table* A - F, 11 - ** for Item
- Get value in C
- Work out if C value is - or + from E (Deposit = +, Withdrawn = -)
- Get current total value sum in I

I think you could only iunderstand if you download the attachment.

Thanks in advanced.

Blaknight


+-------------------------------------------------------------------+
|Filename: help_sKsLeather Blotter.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3836 |
+-------------------------------------------------------------------+

--
Blaknight
------------------------------------------------------------------------
Blaknight's Profile: http://www.excelforum.com/member.php...o&userid=27352
View this thread: http://www.excelforum.com/showthread...hreadid=468591

  #2   Report Post  
PY & Associates
 
Posts: n/a
Default

vlookup only locate the first occurrence of the target. It cannot look
beyond for the 2nd occurrence.

try use find method,
use find (from here) in the 2nd loop
stop loop when list exhausts.

"Blaknight" wrote in
message ...

Hello All,

My first post here as i am very stuck with a very complex formula.

-=See attachment=-

Please if you can help grab the attachment so you know what i am on
about.

This is my problem. I have 6 columns of data labeled
- Item, Date, Qty, Quality, Action, Charater.

What i want is the formula to search A11 to F41 (F41 will be greater
with more data entered) for a word in column A say *Medium Leather*
then look for the value in column C of that word found in column A and
continue.

I have achieved this but when it has found the 1st word it doesn't
continue on to find another word of the same. This is what i have
used.

=VLOOKUP("Medium Leather",A11:F41,3,)

Then on top of that i want it to look in column E for one of two words.
Deposit and Withdrawn. I want the formula to add the data found in
column C when the word Deposit has been found in column E, and
subtracted if the word Withdrawn has been found.

This infomation needs to be placed in the Qty column in the *Totals
Table* next to the corresponding Item.

So. basically i want

- Search Entire *Data Table* A - F, 11 - ** for Item
- Get value in C
- Work out if C value is - or + from E (Deposit = +, Withdrawn = -)
- Get current total value sum in I

I think you could only iunderstand if you download the attachment.

Thanks in advanced.

Blaknight


+-------------------------------------------------------------------+
|Filename: help_sKsLeather Blotter.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3836 |
+-------------------------------------------------------------------+

--
Blaknight
------------------------------------------------------------------------
Blaknight's Profile:

http://www.excelforum.com/member.php...o&userid=27352
View this thread: http://www.excelforum.com/showthread...hreadid=468591



  #3   Report Post  
Max
 
Posts: n/a
Default

One way ..

H12:H24 (except H18) contains the lookup items:

Medium Leather
Heavy Leather
Thick Leather
Rugged Leather
etc

Put in I12:

=SUMPRODUCT(($A$11:$A$100=H12)*($E$11:$E$100="Depo sit"),
$C$11:$C$100)-SUMPRODUCT(($A$11:$A$100=H12)
*($E$11:$E$100="Withdrawn"),$C$11:$C$100)

Copy I12 down to I24

(Clean up by deleting the formula in I18
for the header in H18 where it doesn't apply)

Adapt the ranges to suit, but note that we can't use
entire column references in SUMPRODUCT
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Blaknight" wrote in
message ...

Hello All,

My first post here as i am very stuck with a very complex formula.

-=See attachment=-

Please if you can help grab the attachment so you know what i am on
about.

This is my problem. I have 6 columns of data labeled
- Item, Date, Qty, Quality, Action, Charater.

What i want is the formula to search A11 to F41 (F41 will be greater
with more data entered) for a word in column A say *Medium Leather*
then look for the value in column C of that word found in column A and
continue.

I have achieved this but when it has found the 1st word it doesn't
continue on to find another word of the same. This is what i have
used.

=VLOOKUP("Medium Leather",A11:F41,3,)

Then on top of that i want it to look in column E for one of two words.
Deposit and Withdrawn. I want the formula to add the data found in
column C when the word Deposit has been found in column E, and
subtracted if the word Withdrawn has been found.

This infomation needs to be placed in the Qty column in the *Totals
Table* next to the corresponding Item.

So. basically i want

- Search Entire *Data Table* A - F, 11 - ** for Item
- Get value in C
- Work out if C value is - or + from E (Deposit = +, Withdrawn = -)
- Get current total value sum in I

I think you could only iunderstand if you download the attachment.

Thanks in advanced.

Blaknight


+-------------------------------------------------------------------+
|Filename: help_sKsLeather Blotter.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3836 |
+-------------------------------------------------------------------+

--
Blaknight
------------------------------------------------------------------------
Blaknight's Profile:

http://www.excelforum.com/member.php...o&userid=27352
View this thread: http://www.excelforum.com/showthread...hreadid=468591



  #4   Report Post  
Max
 
Posts: n/a
Default

(Clean up by deleting the formula in I18
for the header in H18 where it doesn't apply)


Likewise for I22 for the other header in H22 as well ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #5   Report Post  
Blaknight
 
Posts: n/a
Default


AWESOME!!! Thankyou so very much.. I got it working. I cant believe it
actualy works..


--
Blaknight
------------------------------------------------------------------------
Blaknight's Profile: http://www.excelforum.com/member.php...o&userid=27352
View this thread: http://www.excelforum.com/showthread...hreadid=468591



  #6   Report Post  
Max
 
Posts: n/a
Default

"Blaknight" wrote:
.. I cant believe it actualy works..


... and I can't believe why the SUMPRODUCT suggestion
didn't work for you. Testing here was fine ..
(btw, you had a typo error in H12, missing "t" in "Leather")
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I test outlier in Excel? Koos jubileert Excel Discussion (Misc queries) 1 August 30th 05 02:32 PM
Statistic: Problem with two one sided t-test Rosario Excel Discussion (Misc queries) 4 May 5th 05 05:23 PM
Excel competency test jonesr2 Excel Discussion (Misc queries) 2 March 14th 05 10:37 PM
=IF logical test to search only part of a cell Robbie in Houston Excel Worksheet Functions 2 March 5th 05 06:09 AM
test..where are my messages..test HT New Users to Excel 0 January 23rd 05 07:23 PM


All times are GMT +1. The time now is 04:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"