Returning information from a correlating cell

[dtlucius]

First a little background to help people understand what I am trying to do. I receive large sales reports in excel and I am trying to bring all of the information into a clean viewable format. I want to have it set up where all I have to do is paste the worksheet into the workbook and the data correlating to a specific product, or customer is retrieved.

The problem I am running into is that the information is not in the same row, or column. I also need to stop retrieving information where the next item code starts. I will attempt to list the details of what I am looking to do below .

1) Q.O.H.--First I need to search for a cell and return a value in the cell located below. In my example in cell F3 I need to search in column A for the item code that is in cell E3 (A01), and return the value of the cell directly below it which is cell A3.

2) Qty on Order---here is where it gets really tricky…..I need to search for the search for the item code and return the sum of the information in column B up to the next cell where there is nothing in it. For example in cell G3 in need to search for the item code that is in E3, go to column A and find that item code and return the sum of what is in column B. I only need it to sum up to row 5 where the information for that item stops.

3) Balance---Here I need to search for the item code and return the information in the last cell correlating with that item in column C. Example in cell H3 I need to go to column A and find the correlating item code and return the information that is in cell C5 which is the last cell in column C that contains information about that Item.

I am not even sure if any of the things I am looking for are even possible, but I figured that this would be the place to find out. If anyone has any information, or or advise this would be greatly appreciated. I was unable to attach a screen shot, or a copy of the excel book. if you are interested in helping me, you can email me at Removed e-mail address - Moderator and I will send you the workbook.

 

[Smitty]

Welcome to the Board!

See the HTML Maker link beneath my sig for how to post a shot of your sheet.

 

[dtlucius]

Chris,
cant quite get it to work.

why cant i have a email address on a post? i am asking for help, why can i not directly conect with someone that is willing to help me?

 

[Smitty]

why cant i have a email address on a post? i am asking for help, why can i not directly conect with someone that is willing to help me?

You can have your e-mail address in your post if you insist, but we discourage it, because then you're a target for multiple spambots and other nasty things that scour the internet for vulnerable e-mail addresses. If you want to contact someone directly you can use the PM feature.

However, since this is a public forum we don't encourage offline discussions for a few reasons: 1) you limit the number of people who can potentially help you by going offline and 2) you limit the effectiveness of your thread with regards to the number of people who might be in a similar situation and find your it useful.

As for how to get the best results, make sure that you have adequate descriptions and don't try to bite off too much at once.

From what I can see of your issue you might want to try either AutoFilter or a Pivot Table.