Dear Sir/mam,
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.

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

I hope this plateform will definetly solve my problem soon.

Since there's only 3 sheets, why not just use 3 =sumif()'s.

=sumif(...) + sumif(...) + sumif(...)

Just point to the ranges each of the sheets when you're building that part of
the formula.

For what it's worth, I'd try to keep the data in one worksheet--you could always
add another column that could server as some kind of indicator. It really makes
life a lot easier when the data is consolidated.

Rao Ratan Singh wrote:

Dear Sir/mam,
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.

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

I hope this plateform will definetly solve my problem soon.

--

Dave Peterson

Dear Dave,
Thanks for reply but problem is every sheet inventory ID starts from 01. so
it is difficult that ID code in daily report may be of Paintings, Textiles or
Jewellery. Thats why i asked tha when ID No. Match then Column "Text" Match
after that the formula detects value from respective sheet.

Hope u will once try to understand my problem.


"Dave Peterson" wrote:

Since there's only 3 sheets, why not just use 3 =sumif()'s.

=sumif(...) + sumif(...) + sumif(...)

Just point to the ranges each of the sheets when you're building that part of
the formula.

For what it's worth, I'd try to keep the data in one worksheet--you could always
add another column that could server as some kind of indicator. It really makes
life a lot easier when the data is consolidated.

Rao Ratan Singh wrote:

Dear Sir/mam,
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.

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

I hope this plateform will definetly solve my problem soon.

--

Dave Peterson

If you can come up with rules how to determine which sheet should be used, maybe
you could use =indirect() in your formula.

=SUMIF(INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2", "sheet3"))&"!a:a"),
B1,INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2","she et3"))&"!C:C"))

But you'd have to know some sort of rule.

Rao Ratan Singh wrote:

Dear Dave,
Thanks for reply but problem is every sheet inventory ID starts from 01. so
it is difficult that ID code in daily report may be of Paintings, Textiles or
Jewellery. Thats why i asked tha when ID No. Match then Column "Text" Match
after that the formula detects value from respective sheet.

Hope u will once try to understand my problem.

"Dave Peterson" wrote:

Since there's only 3 sheets, why not just use 3 =sumif()'s.

=sumif(...) + sumif(...) + sumif(...)

Just point to the ranges each of the sheets when you're building that part of
the formula.

For what it's worth, I'd try to keep the data in one worksheet--you could always
add another column that could server as some kind of indicator. It really makes
life a lot easier when the data is consolidated.

Rao Ratan Singh wrote:

Dear Sir/mam,
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.

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

I hope this plateform will definetly solve my problem soon.

--

Dave Peterson


--

Dave Peterson

Dear Sir,
If you give permission and your email ID i want to send Inventory & Daily
Sales Report directly to you for better understanding of problem.

Regards

Rao Ratan Singh

"Dave Peterson" wrote:

If you can come up with rules how to determine which sheet should be used, maybe
you could use =indirect() in your formula.

=SUMIF(INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2", "sheet3"))&"!a:a"),
B1,INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2","she et3"))&"!C:C"))

But you'd have to know some sort of rule.

Rao Ratan Singh wrote:

Dear Dave,
Thanks for reply but problem is every sheet inventory ID starts from 01. so
it is difficult that ID code in daily report may be of Paintings, Textiles or
Jewellery. Thats why i asked tha when ID No. Match then Column "Text" Match
after that the formula detects value from respective sheet.

Hope u will once try to understand my problem.

"Dave Peterson" wrote:

Since there's only 3 sheets, why not just use 3 =sumif()'s.

=sumif(...) + sumif(...) + sumif(...)

Just point to the ranges each of the sheets when you're building that part of
the formula.

For what it's worth, I'd try to keep the data in one worksheet--you could always
add another column that could server as some kind of indicator. It really makes
life a lot easier when the data is consolidated.

Rao Ratan Singh wrote:

Dear Sir/mam,
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.

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

I hope this plateform will definetly solve my problem soon.

--

Dave Peterson


--

Dave Peterson

No thanks.

You'd still have to explain what you want to do. And if you do that in this
forum, maybe someone else will jump in with the answer you want.

Rao Ratan Singh wrote:

Dear Sir,
If you give permission and your email ID i want to send Inventory & Daily
Sales Report directly to you for better understanding of problem.

Regards

Rao Ratan Singh

"Dave Peterson" wrote:

If you can come up with rules how to determine which sheet should be used, maybe
you could use =indirect() in your formula.

=SUMIF(INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2", "sheet3"))&"!a:a"),
B1,INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2","she et3"))&"!C:C"))

But you'd have to know some sort of rule.

Rao Ratan Singh wrote:

Dear Dave,
Thanks for reply but problem is every sheet inventory ID starts from 01. so
it is difficult that ID code in daily report may be of Paintings, Textiles or
Jewellery. Thats why i asked tha when ID No. Match then Column "Text" Match
after that the formula detects value from respective sheet.

Hope u will once try to understand my problem.

"Dave Peterson" wrote:

Since there's only 3 sheets, why not just use 3 =sumif()'s.

=sumif(...) + sumif(...) + sumif(...)

Just point to the ranges each of the sheets when you're building that part of
the formula.

For what it's worth, I'd try to keep the data in one worksheet--you could always
add another column that could server as some kind of indicator. It really makes
life a lot easier when the data is consolidated.

Rao Ratan Singh wrote:

Dear Sir/mam,
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.

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

I hope this plateform will definetly solve my problem soon.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dear Sir,
Please tell me in which cell i should put this formula & How it determin the
value after matching ID No. and Name of Item in Daily Report

=SUMIF(INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2", "sheet3"))&"!a:a"),
B1,INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2","she et3"))&"!C:C"))

Please clear me A1 for what an which range?
a:a for which range

My Daily Report Format is -
A B C D
ID No. Name of Item Cost Price Sale Price
001 Paintings
002 Silver
003 Textile
001 Silver
002 Paintings

I as you know that in every sheet ID No. starts from 001. So ID No. 001 may
be of Painting and may be of Silver or Textile.

I want to determine Cost price from three of sheets. Sheets had renamed that
is "Paintings", "Silver", and "Textiles".

I hope now it is clear to understand my problem.



"Dave Peterson" wrote:

If you can come up with rules how to determine which sheet should be used, maybe
you could use =indirect() in your formula.

=SUMIF(INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2", "sheet3"))&"!a:a"),
B1,INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2","she et3"))&"!C:C"))

But you'd have to know some sort of rule.

Rao Ratan Singh wrote:

Dear Dave,
Thanks for reply but problem is every sheet inventory ID starts from 01. so
it is difficult that ID code in daily report may be of Paintings, Textiles or
Jewellery. Thats why i asked tha when ID No. Match then Column "Text" Match
after that the formula detects value from respective sheet.

Hope u will once try to understand my problem.

"Dave Peterson" wrote:

Since there's only 3 sheets, why not just use 3 =sumif()'s.

=sumif(...) + sumif(...) + sumif(...)

Just point to the ranges each of the sheets when you're building that part of
the formula.

For what it's worth, I'd try to keep the data in one worksheet--you could always
add another column that could server as some kind of indicator. It really makes
life a lot easier when the data is consolidated.

Rao Ratan Singh wrote:

Dear Sir/mam,
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.

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

I hope this plateform will definetly solve my problem soon.

--

Dave Peterson


--

Dave Peterson


"Dave Peterson" wrote:

If you can come up with rules how to determine which sheet should be used, maybe
you could use =indirect() in your formula.

=SUMIF(INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2", "sheet3"))&"!a:a"),
B1,INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2","she et3"))&"!C:C"))

But you'd have to know some sort of rule.

Rao Ratan Singh wrote:

Dear Dave,
Thanks for reply but problem is every sheet inventory ID starts from 01. so
it is difficult that ID code in daily report may be of Paintings, Textiles or
Jewellery. Thats why i asked tha when ID No. Match then Column "Text" Match
after that the formula detects value from respective sheet.

Hope u will once try to understand my problem.

"Dave Peterson" wrote:

Since there's only 3 sheets, why not just use 3 =sumif()'s.

=sumif(...) + sumif(...) + sumif(...)

Just point to the ranges each of the sheets when you're building that part of
the formula.

For what it's worth, I'd try to keep the data in one worksheet--you could always
add another column that could server as some kind of indicator. It really makes
life a lot easier when the data is consolidated.

Rao Ratan Singh wrote:

Dear Sir/mam,
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.

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

I hope this plateform will definetly solve my problem soon.

--

Dave Peterson


--

Dave Peterson

It's still not clear to me. Maybe it's clear to someone else.

Rao Ratan Singh wrote:

Dear Sir,
Please tell me in which cell i should put this formula & How it determin the
value after matching ID No. and Name of Item in Daily Report

=SUMIF(INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2", "sheet3"))&"!a:a"),
B1,INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2","she et3"))&"!C:C"))

Please clear me A1 for what an which range?
a:a for which range

My Daily Report Format is -
A B C D
ID No. Name of Item Cost Price Sale Price
001 Paintings
002 Silver
003 Textile
001 Silver
002 Paintings

I as you know that in every sheet ID No. starts from 001. So ID No. 001 may
be of Painting and may be of Silver or Textile.

I want to determine Cost price from three of sheets. Sheets had renamed that
is "Paintings", "Silver", and "Textiles".

I hope now it is clear to understand my problem.

"Dave Peterson" wrote:

If you can come up with rules how to determine which sheet should be used, maybe
you could use =indirect() in your formula.

=SUMIF(INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2", "sheet3"))&"!a:a"),
B1,INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2","she et3"))&"!C:C"))

But you'd have to know some sort of rule.

Rao Ratan Singh wrote:

Dear Dave,
Thanks for reply but problem is every sheet inventory ID starts from 01. so
it is difficult that ID code in daily report may be of Paintings, Textiles or
Jewellery. Thats why i asked tha when ID No. Match then Column "Text" Match
after that the formula detects value from respective sheet.

Hope u will once try to understand my problem.

"Dave Peterson" wrote:

Since there's only 3 sheets, why not just use 3 =sumif()'s.

=sumif(...) + sumif(...) + sumif(...)

Just point to the ranges each of the sheets when you're building that part of
the formula.

For what it's worth, I'd try to keep the data in one worksheet--you could always
add another column that could server as some kind of indicator. It really makes
life a lot easier when the data is consolidated.

Rao Ratan Singh wrote:

Dear Sir/mam,
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.

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

I hope this plateform will definetly solve my problem soon.

--

Dave Peterson


--

Dave Peterson


"Dave Peterson" wrote:

If you can come up with rules how to determine which sheet should be used, maybe
you could use =indirect() in your formula.

=SUMIF(INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2", "sheet3"))&"!a:a"),
B1,INDIRECT(IF(A1=1,"Sheet1",IF(A1=2,"sheet2","she et3"))&"!C:C"))

But you'd have to know some sort of rule.

Rao Ratan Singh wrote:

Dear Dave,
Thanks for reply but problem is every sheet inventory ID starts from 01. so
it is difficult that ID code in daily report may be of Paintings, Textiles or
Jewellery. Thats why i asked tha when ID No. Match then Column "Text" Match
after that the formula detects value from respective sheet.

Hope u will once try to understand my problem.

"Dave Peterson" wrote:

Since there's only 3 sheets, why not just use 3 =sumif()'s.

=sumif(...) + sumif(...) + sumif(...)

Just point to the ranges each of the sheets when you're building that part of
the formula.

For what it's worth, I'd try to keep the data in one worksheet--you could always
add another column that could server as some kind of indicator. It really makes
life a lot easier when the data is consolidated.

Rao Ratan Singh wrote:

Dear Sir/mam,
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.

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

I hope this plateform will definetly solve my problem soon.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson