To Max MVP
 

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.

To Max MVP
 

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.

To Max MVP
 

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

To Max MVP
 

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

Thank you Roger
 

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

Thank you Roger
 

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

Thank you Roger
 

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

Regards

"Roger Govier" wrote:

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

Thank you Roger
 

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.


--
Regards

Roger Govier


"Rao Ratan Singh" wrote in
message ...
It is returning #value!. and I doubled cost price in Painting and
Textiles
Sheet.
How to change in formula.

Regards

"Roger Govier" wrote:

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

Thank you Roger
 

Hello Sir,
I have checked all sheets your doubt but nothing is there (l00). May be
something other error.

Regards

Rao Ratan Singh

"Roger Govier" wrote:

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.


--
Regards

Roger Govier


"Rao Ratan Singh" wrote in
message ...
It is returning #value!. and I doubled cost price in Painting and
Textiles
Sheet.
How to change in formula.

Regards

"Roger Govier" wrote:

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

Thank you Roger
 

Ok Sir it is working. The error was due to I was not entering column number
of respective sheets not entred right.

Thank you very much.

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

Regards

Thank you Roger
 

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?

--
Regards

Roger Govier


"Rao Ratan Singh" wrote in
message ...
Ok Sir it is working. The error was due to I was not entering column
number
of respective sheets not entred right.

Thank you very much.

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

Regards

Sir
 

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.

"Roger Govier" wrote:

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?

--
Regards

Roger Govier


"Rao Ratan Singh" wrote in
message ...
Ok Sir it is working. The error was due to I was not entering column
number
of respective sheets not entred right.

Thank you very much.

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

Regards