To Max MVP

[Rao Ratan Singh]

Dear Sir,
Thank you for your quick response. I have already post my problem to this
platform. But I m unable to clear my problem so nobody is properly giving me
answer. I hope u will definetly solve my problem.

I have three worksheets containing inventory record of paintings, Jewellery
and Textiles.

All have same column = IDNo. Name of Item Price

I submit daily sales report to my boss which have column in this manner-

A B C D E
ID No. Name of Item Cost Price Sale Price Gain/Loss

I m using this function to track value from Painting
=SUMIF(Pntng_ID,B20,Pntng_Cost) in
column C.

But How to determine value from three of sheets with two criteria.
Is there any formula to track value from all three sheets putting
"paintings", "Jewellery" and
"Textiles" in column B and in column C the formula can track value according
to Colum B's
criteria.

I hope this plateform will definetly solve my problem soon.

 

[Roger Govier]

Hi

If the formula is working for you with Paintings, and, assuming the ID
is unique for any range, then just have 3 Sumif's
in the cell
=SUMIF(Pntng_ID,B20,Pntng_Cost)+SUMIF(Jewlry_ID,B20,Jewlry_Cost)+SUMIF(Txtl_ID,B20,Txtl_Cost)

I would prefer to have all the data on a single sheet, with an extra
column to determine either Painting, Jewellery or Textile.
Then use an Autofilter to select the category I wanted to look at.
Equally, if there were a column for Sale date, a simple filter by that
column for the day, would give the report required across all
categories.

Finally, with data in that format, a Pivot Table would provide all sorts
of useful analysis.

 

[Roger Govier]

Hi

If that is the case, then supposing that on sheet Textile, you have your
ID's in column A and their Cost in column B.
Set up a defined name called Textile, with a range of say $A1:$B1000 or
sufficient to cover your product list.
Create names also for Silver and Painting

Then on your Daily Report sheet in cell C2
=IF(A2="","",VLOOKUP(A2,INDIRECT(B2),2,0))

 

[Roger Govier]

Hi

Amend the formula to
=IF(A2="","",VLOOKUP(A2,INDIRECT(B2),2,0)*(1-(50%*--(B2<>"Silver"))))

If the entry is not from the Silver sheet, it will take 50% of the value
found on the other sheets.

 

[Rao Ratan Singh]

It is returning #value!. and I doubled cost price in Painting and Textiles
Sheet.
How to change in formula.

Regards

 

[Roger Govier]

Hi

It works fine for me. It sounds as though one of the values in your
table is not correct.
Perhaps you have entered a lower case letter L followed by two zero's
( l00 ) instead of 100 somewhere in the table

and I doubled cost price in Painting and Textiles

Yes, which means that if the entry in column B is not equal to Silver,
(B2<>"Silver")
then it must be Textiles or Painting, and it will take 50% of the value
found.

 

[Roger Govier]

Hi
Glad you got it working.

One thing More. Can it recorded as like database datewise.

Nor sure what you mean here. Could you give a little more explanation?

 

[Rao Ratan Singh]

Sir,
I want one thing more that when I enter a particular number and Item name,
automatically that particular Item should be freez in the respective sheet or
in that sheet "sold" entered automatically.