• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help..."it just doesn't add up"!

K

Kandi

New Member
=IF(ISBLANK($A$66:$A$543),"",(IF(ISBLANK(E$2),"",(VLOOKUP($A$66:$A$543,Sheet1!$A$4:$AA$397,HLOOKUP($B$2:$AI$2,Sheet1!$B$1:$Z$2,2,FALSE),FALSE)))))

Above is my formula. This formula is on my report page. My sheet1! is where we enter the data needed for the report. The only thing that seems to be a problem is that it does not "sum" two identical customers.
This is a spot of my report:
Bravos Supply
CalPly Pico Rivera L&W 1
Calply Sun Valley L&W
Charles Hardy Co, Paramount, CA
Foundation Building Materials - Orange, CA
Foundation Building Materials - N HOLLY 1
Glesby Building Materials
Great Western Bldg Material - Oxnard
Great Western Bldg Material - Pico Rivera 1
JC Building Supply
Jones Lumber, Lynwood, CA
Lowe's - Norwalk
Nichols Lumber, Baldwin Park CA

This is a spot of Sheet1!:
CalPly Pico Rivera L&W 1
Calply Sun Valley L&W
Foundation Building Materials - N HOLLY 1
Foundation Building Materials - Orange, CA
Charles Hardy Co, Paramount, CA
Great Western Bldg Material - Oxnard
Great Western Bldg Material - Pico Rivera 1
JC Building Supply
Nichols Lumber, Baldwin Park CA

What happens with my report is: We enter data on Sheet1!
and after formula it registers in the report. The problem I am
having is when a customer "Calply Pico Rivera L&W is on my
data sheet twice because of different warehouses I cannot get
my "report" to add common name entries "1" + "1" that each
have a numerical data entry.
Wow does this make sense....I sure hope so.
 
Thanks. My file was too large. I pasted the main report on sheet 1 and a portion of my Sheet1! onto sheet 2 of this file.

Hope this helps.
 

Attachments

  • upload for help.xlsx
    162.9 KB · Views: 8
Slap dash. Should have been:
=IF(ISBLANK($A$66:$A$543),"",(IF(ISBLANK(E$2),"",(VLOOKUP($A$66:$A$543,Sheet2!$A$4:$AA$397,HLOOKUP($B$2:$AI$2,Sheet2!$B$1:$Z$2,2,FALSE),FALSE)))))
placed somewhere on sheet1.

Try in cell E227 of Sheet1:
=IF(ISBLANK($A227),"",IF(ISBLANK(E$2),"",SUMIF(Sheet2!$A$4:$A$397,$A227,OFFSET(Sheet2!$A$4:$A$397,0,E$2))))

copy up/down/across/wherever.
 
I think that is the ticket! Thank you for now...I will let you know the outcome as soon as I finish up the copying of formula and run a test.

p45cal
 
Good morning, it appears to not work exactly as I thought. I am going to try and explain it a little differently.
On Sheet 1 in my upload is where my formula will need to go. My current formula that I have is posted in this thread.
What I need it to do is:
lookup the customer name from sheet 1 from sheet 2 and take the number entered for that customer on sheet 2 back to the customer on sheet 1. However, a single customer is sometimes on sheet 2 in several locations. Therefore, I need the identical customers from sheet 2 to total and populate on sheet 1 at that customers name that was looked up. This formula is needed in the areas across sheet 1 where there is a customer name. Your help is greatly appreciated.

I have downsized my original report and included a new upload in this post.
 

Attachments

  • Las Vegas June 1, 2016.xlsx
    450.7 KB · Views: 6
Last edited:
Kandi said:
I think that is the ticket! Thank you for now...I will let you know the outcome as soon as I finish up the copying of formula and run a test.

p45cal
Click to expand...
IT did not work. It populated other customers with a 2 who were not marked on sheet 2. Please see my new upload. It is what I am working on exactly.
 
Try this formula in E227 of "June 2016" sheet.
=IF(OR(ISBLANK($A227),ISBLANK(E$2)),"",SUMPRODUCT((Sheet1!$A$3:$A$393=$A227)*(Sheet1!$B$1:$Z$1=E$2),Sheet1!$B$3:$Z$393))

Copy to where ever you need the formula in C:AJ. I'm assuming that numbers in C2:AJ2 in "June 2016" sheet corresponds to numbers in "Sheet1" B1:Z1.
 
Chihiro said:
Try this formula in E227 of "June 2016" sheet.
=IF(OR(ISBLANK($A227),ISBLANK(E$2)),"",SUMPRODUCT((Sheet1!$A$3:$A$393=$A227)*(Sheet1!$B$1:$Z$1=E$2),Sheet1!$B$3:$Z$393))

Copy to where ever you need the formula in C:AJ. I'm assuming that numbers in C2:AJ2 in "June 2016" sheet corresponds to numbers in "Sheet1" B1:Z1.
Click to expand...
That did not work either. Where there is no number in cell c2 on June 2016; there should be no number in that column for any customer. When I entered your formula I got the number "2" in column c for three different customers. This is when I stopped and decided to reply to you. Would you like to see my workbook again where these 2's populated. My original formula in my original post works very well...it is just not identifying identical customer names from sheet 1 and adding them together in the proper cell on June 2016.
 
Are you sure you are copying formula from E227 to Column C?
Formula in C17 would be...
=IF(OR(ISBLANK($A17),ISBLANK(C$2)),"",SUMPRODUCT((Sheet1!$A$3:$A$393=$A17)*(Sheet1!$B$1:$Z$1=C$2),Sheet1!$B$3:$Z$393))
 
Chihiro said:
Are you sure you are copying formula from E227 to Column C?
Formula in C17 would be...
=IF(OR(ISBLANK($A17),ISBLANK(C$2)),"",SUMPRODUCT((Sheet1!$A$3:$A$393=$A17)*(Sheet1!$B$1:$Z$1=C$2),Sheet1!$B$3:$Z$393))
Click to expand...

Yes I did copy. However, what I didn't do was change the number in the formula you gave me when I had to jump a row or several rows. Everything is taking shape. Do you mind helping me one more time? If you look at the bottom of June 2016 you will see a place with total areas. These formulas used to add up like customers (such as all Foundation=FBM) But now that there is a formula in the cell it use to sum up...I am not sure how to fix this. I am giving it try.
Thank you so much for all your help thus far.
 
When you copy from one cell to another (without double clicking on the cell), formula will auto adjust.

You can create lookup table each of category and use that to do your calculation. But it it were up to me, I'd re-organize data and calculation table to be more database like structure and use PivotTables to summarize data.
 
Chihiro said:
When you copy from one cell to another (without double clicking on the cell), formula will auto adjust.

You can create lookup table each of category and use that to do your calculation. But it it were up to me, I'd re-organize data and calculation table to be more database like structure and use PivotTables to summarize data.
Click to expand...
Thanks again. I have the formula working for those lower cells. I truly appreciate all your help. I have to run a full test on my report. I will let you know how that goes. :)
 
You must log in or register to reply here.
Back
Top