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.

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,B2 0,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.


--
Regards

Roger Govier


"Rao Ratan Singh" wrote in
message ...
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.

Dear Roger,
Thank you for reply. But problem is value can be determine after matching to
criteria. First ID No. which all are starting from 001 but its range name is
different that is Pntng_ID, Jwlry_ID and Txtl_ID. After that its second
criteria should match that is it from sheet Painting or Silver or Textile.

and one onother i dont want to + of determined value. It will seperately
entered in next row.

My Daily Report Format is -
IDNo. Sheet Name Cost Price
0001 Textile =Formula (What is expected from you)
0002 Silver
0002 Painting
0003 Painting
0004 Silver
0006

Because after ID No. and Sheet Name is two criteria if found matching then
it should return proper cost value from respective cell.

Regards

RRS

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


--
Regards

Roger Govier


"Rao Ratan Singh" wrote in
message ...
Dear Roger,
Thank you for reply. But problem is value can be determine after
matching to
criteria. First ID No. which all are starting from 001 but its range
name is
different that is Pntng_ID, Jwlry_ID and Txtl_ID. After that its
second
criteria should match that is it from sheet Painting or Silver or
Textile.

and one onother i dont want to + of determined value. It will
seperately
entered in next row.

My Daily Report Format is -
IDNo. Sheet Name Cost Price
0001 Textile =Formula (What is expected from you)
0002 Silver
0002 Painting
0003 Painting
0004 Silver
0006

Because after ID No. and Sheet Name is two criteria if found matching
then
it should return proper cost value from respective cell.

Regards

RRS

Thankyou Sir,
Thank you very much. It is unbelieavalbe but true. It is working.

But one thing more . In Painting and Textile sheet I have doubled the cost
price but in daily report i want to determine its actual what is 1/2 of value
entered in sheets.

The how i should entered formula.

Regards

Rao Ratan Singh
"Roger Govier" wrote:

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


--
Regards

Roger Govier


"Rao Ratan Singh" wrote in
message ...
Dear Roger,
Thank you for reply. But problem is value can be determine after
matching to
criteria. First ID No. which all are starting from 001 but its range
name is
different that is Pntng_ID, Jwlry_ID and Txtl_ID. After that its
second
criteria should match that is it from sheet Painting or Silver or
Textile.

and one onother i dont want to + of determined value. It will
seperately
entered in next row.

My Daily Report Format is -
IDNo. Sheet Name Cost Price
0001 Textile =Formula (What is expected from you)
0002 Silver
0002 Painting
0003 Painting
0004 Silver
0006

Because after ID No. and Sheet Name is two criteria if found matching
then
it should return proper cost value from respective cell.

Regards

RRS

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.

--
Regards

Roger Govier


"Rao Ratan Singh" wrote in
message ...
Thankyou Sir,
Thank you very much. It is unbelieavalbe but true. It is working.

But one thing more . In Painting and Textile sheet I have doubled the
cost
price but in daily report i want to determine its actual what is 1/2
of value
entered in sheets.

The how i should entered formula.

Regards

Rao Ratan Singh
"Roger Govier" wrote:

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


--
Regards

Roger Govier


"Rao Ratan Singh" wrote in
message ...
Dear Roger,
Thank you for reply. But problem is value can be determine after
matching to
criteria. First ID No. which all are starting from 001 but its
range
name is
different that is Pntng_ID, Jwlry_ID and Txtl_ID. After that its
second
criteria should match that is it from sheet Painting or Silver or
Textile.

and one onother i dont want to + of determined value. It will
seperately
entered in next row.

My Daily Report Format is -
IDNo. Sheet Name Cost Price
0001 Textile =Formula (What is expected from you)
0002 Silver
0002 Painting
0003 Painting
0004 Silver
0006

Because after ID No. and Sheet Name is two criteria if found
matching
then
it should return proper cost value from respective cell.

Regards

RRS