To Max MVP

R

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.
 
R

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.
 
R

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))
 
R

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.
 
R

Rao Ratan Singh

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

Regards
 
R

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.
 
R

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?
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top