# Posts by firroo

• ## \$15 - formula to match data between 2 sheets

\$1.5 paid to ozgrid paypal account
Transaction ID:10Y597439K3742005

Dear Experts,

Can anyone help me to find a formula for the request I have. In the attached file you will see 2 sheets (BOE & LOC). What I need that in the LOC sheet that in column D that the formula looks at the part number (column A), Qty (Column B), & location (column C) and then give me the BOE's from BOE sheet that matches the 3 criteria's that I mentioned. For example in LOC sheet you will find part # AF2524600 with 6 qty under location P-C19-02-1US, the formula in column D have to check the BOE's numbers that matches the above criteria's from BOE sheet, the result will be in that cell that it will show 3 numbers [TABLE="width: 80"]

[tr]

[td]

142414982992

[/td]

[/tr]

[tr]

[td]

142415000143

[/td]

[/tr]

[tr]

[td]

142415000143
as the numbers sum above of the qty's are 6 and have the same location & part # if we compare it what we have in LOC sheet.

[/td]

[/tr]

[/TABLE]
[TABLE="width: 80"]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[/TABLE]

• ## conditional formatting based on text & blanks

Re: conditional formatting based on text &amp; blanks

It didn't work with me the counta, but when I changed it to countblank it did the job. Thanks for your help.

Quote from NBVC;762192

Select frome A2:AI2 and go to Home|Conditional Formatting|New Rule

Choose "use a formula to determine which cells to format" then enter formula:

=AND(\$AF2="cancelled",Counta(\$AE2:\$AH2)=0)

Cllick Format and choose from Fill tab.

• ## conditional formatting based on text & blanks

Hi Experts,
I have in cell AF2 a drop list, if a word "cancelled" being chosen & from cell AE2 to AH2 is blank, I want to highlight the full row from A2 to AI2 with yellow color. How can this be done with conditional formatting??
Thank you

• ## Order Backlog formula

Re: Order Backlog formula

Seems its difficult formula

• ## Order Backlog formula

Re: Order Backlog formula

• ## Order Backlog formula

Re: Order Backlog formula

Thank you Inag
As requested I attached a sample of the file.

Quote from inag;758978

Can you attach a sample copy of workbook ?

• ## Order Backlog formula

Hi Experts,

I need your help regarding order backlog formula.

In sheet 1 I have the order data with many details, the important columns are column (D) which will have the delivery month & column (AV) which have the branch that the sale for.

In sheet 2 I do have a drop down list where I choose the month in column (B2) which is Sep'15 and then a table like the below. On order backlog I want a formula that will calculate the sum of orders from oct'15 to Dec'15 by branch, If I choose the month Oct'15 in column (B2) then formula will be sum from Nov'15 to Dec'15 for each branch and so on, even if the chosen month went to year 2016 lets say Feb I will still get the sum from March,16 to Dec'16

[TABLE="width: 397"]

[tr]

[td]

Branch

[/td]

[td]

Sales YTD

[/td]

[td]

Order Backlog

[/td]

[td]

GM% Sales YTD

[/td]

[/tr]

[tr]

[td]

XYZ

[/td]

[TD="align: right"]11,310[/TD]

[td][/td]

[TD="align: right"]19.97%[/TD]

[/tr]

[tr]

[td]

ABC

[/td]

[TD="align: right"]7,166[/TD]

[td][/td]

[TD="align: right"]16.50%[/TD]

[/tr]

[tr]

[td]

DFG

[/td]

[TD="align: right"]7,508[/TD]

[td][/td]

[TD="align: right"]16.79%[/TD]

[/tr]

[tr]

[td]

RTS

[/td]

[TD="align: right"]3,298[/TD]

[td][/td]

[TD="align: right"]14.36%[/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[td][/td]

[/tr]

[/TABLE]
[TABLE="width: 397"]

[tr]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

[/td]

[TD="align: right"][/TD]

[td][/td]

[TD="align: right"][/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"][/TD]

[td][/td]

[TD="align: right"][/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"][/TD]

[td][/td]

[TD="align: right"][/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"][/TD]

[td][/td]

[TD="align: right"][/TD]

[/tr]

[tr]

[td][/td]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[td][/td]

[/tr]

[/TABLE]

• ## Sum with condition

Re: Sum with condition

Hi,

Thank you so much. This is so great, I appreciate your help.

Wish you a good day.

• ## Sum with condition

Re: Sum with condition

Hi,

Thanks for the help.

For June the numbers% is incorrect, but for July its correct??

Best regards

Quote from Batman;754017

Does this work? You can hide column C to avoid seeing the helper data.

• ## Sum with condition

Re: Sum with condition

Thank you for your interesting to help.

good questions.

1. I get the data for enquiries & wons from other sheet by using vlookup.

2. Yes year will be added to the date, I was in hurry so that's why I mentioned the months without years, but in orginal format years will be there.

I wish if an solution will be given. Thank you

Quote from Batman;754009

1. Where do you store, or how do you identify, the current month to form the basis of the calculations?

2. You don't have a year identifier anywhere in the data. What is going to happen when you get to January? Do you compare across years, or do you not produce results for the first 5 months of the year?

• ## Sum with condition

Re: Sum with condition

Hi,

Thanks for showing your interest in helping me.

1. Yes if I use the formula you mentioned I get what I need.
2. The answer is no my data is not sorted properly that's why I was looking for a formula to help me , maybe vlookup with match & index or other formula that I don't know.

Best regards

• ## Sum with condition

forum.ozgrid.com/index.php?attachment/66570/

Hello all,

Can any one please advise on a formula. in the attach file I want a formula on each rate cell for month june calculate sum of June,may,Apr wons's didvided by sum of mar.feb,jan enquiries.

When I will move to July it will be sum of Jul,Jun,May won's didvided by sum of Apr,Mar,Feb enquiries. If I will drag the formula late to august it should calculate in the same way.

• ## X axis in pivot chart shows decimal than percentage

Re: X axis in pivot chart shows decimal than percentage

Quote from Luke M;726629

There is a file size limit...perhaps try to trim things down, or upload a single sheet?

• ## X axis in pivot chart shows decimal than percentage

Re: X axis in pivot chart shows decimal than percentage

Hi Luke, Thank you for your interest in helping me. I am trying to attach & upload the file several times, but it wouldn't and I don't know the reason for that, I will check again later. Thank you so much.

Quote from Luke M;726489

Sorry for asking again, but could you upload to forum directly? I can't access outside sites.

• ## X axis in pivot chart shows decimal than percentage

Re: X axis in pivot chart shows decimal than percentage

Quote from Luke M;726468

Can you upload your workbook? MY only guess at this point would be that the numbers are stored as text, but that seems highly unlikely.

Hi Luke,

Thank for interesting in helping me with the issue. I uploaded the file as you request.

Best regards

The link for the file: https://www.dropbox.com/s/izc0…v/pivot%20chart.xlsx?dl=0

• ## X axis in pivot chart shows decimal than percentage

Hi All,

I had created a pivot table and pivot chart for the data, the pivot table shows the row label's as percentages which is correct, but it when comes to the pivot chart it shows it as decimal, for example a 10% shows 0.1, I tried of formatting the x axis and choose percentages but it dosent want to change. Please help.

Thanks

• ## column chart with line chart & percentage

Re: column chart with line chart &amp; percentage

Thank you so much for your help. This great. I have a question related to this, can the purchases be line trend and on the dots it shows the percentage, so I will have one column graph and one line only.

Quote from pangolin;725101

see if the attached file helps

• ## column chart with line chart & percentage

Re: column chart with line chart &amp; percentage

• ## column chart with line chart & percentage

Hi All,

Hope everyone keeps doing ok.

In the attached file I have the data which shows me the market size in value, my market share in value & I did a calculation to get the percentage. What I need is a chart that shows me the market size in column graph, my market share in line graph and I want the percentages to be showing also.

• ## Sumif with date criteria

Re: Sumif with date criteria

Thank you so much Pangolin, it works perfectly for the first part. Sorry that the second part was confusing, so I will put it into another sentence, so the weeks will not depend on the months (B2) I will be choosing any week for example week 10 & year 2014, so the formula will calculate the sales from weeks 1 to 9 for year 2014, I hope that's its clear and you can help me with an answer.

Thanks

Quote from pangolin;724370

for the first formula YTD Sales Months - Current Month use this

=SUMPRODUCT((sales!B3:B1520)*(YEAR(sales!A3:A1520)=YEAR(B2))*(MONTH(sales!A3:A1520)<MONTH(B2)))

for the second it is not very clear since as per your list WS for 2014 the week numbers start from 25 till 36 and in the sales ws the date format are in DD/DD/YYYY format (ie the days are equal to month)...hence it is not very clear how the week numbers are to be identified...but in any case the formula will be somewhat similar to the one above