Hi,
I have 10 sheet for inventory record of Paintings, Silver Jewllery,
Textiles, Wooden Items etc. I track cost price of entering its ID number by
using this this formula
=IF(A523="","",VLOOKUP(A523,INDIRECT(B523),10,0)*( 1-(50%*--(B523<"slvrJwllry_Articles")))) to track specific value from respecive sheet.

But I want to do more. Is there any way when I enter a IDnumber of
particular item, then in the respective sheet this item number's detail
automatically freeze and change its color.

I don't know what you mean by freezing the item (can you explain?), but
having it change colors is fairly easy.

Give cell A523 a name (like ItemIDQuery). Then on each product sheet,
select the entire data table and apply conditional formatting using Formula
Is.
=$A2=ItemIDQuery

Select the formatting you want and then whenever the item ID matches a
particular ID in column A of your product worksheet, that entire row will
change to the formatting you want.

- KC Rippstein
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:

Hi,
I have 10 sheet for inventory record of Paintings, Silver Jewllery,
Textiles, Wooden Items etc. I track cost price of entering its ID number by
using this this formula
=IF(A523="","",VLOOKUP(A523,INDIRECT(B523),10,0)*( 1-(50%*--(B523<"slvrJwllry_Articles")))) to track specific value from respecive sheet.

But I want to do more. Is there any way when I enter a IDnumber of
particular item, then in the respective sheet this item number's detail
automatically freeze and change its color.

Thanks for response.
I wnat to do change color or put text "sold" in the particular item which
has sold.

For your better understand my case i want to give some more detail to you.

I have a workbook which have 5 worksheets. 4 worksheets haveinventory record
of different items and 1 worksheet for my daily sale report, its column is-

ID, Name of Item, CostPrice SalePrice, Loss/Gain

I use just to know gross profit of the day. After taking print out I delete
its data.

In inventory sheets i want to color change or putting text "sold" in
respective sheet permanantely using just after sale report data entered.

Is there any way.

Regards

"KC Rippstein" wrote:

I don't know what you mean by freezing the item (can you explain?), but
having it change colors is fairly easy.

Give cell A523 a name (like ItemIDQuery). Then on each product sheet,
select the entire data table and apply conditional formatting using Formula
Is.
=$A2=ItemIDQuery

Select the formatting you want and then whenever the item ID matches a
particular ID in column A of your product worksheet, that entire row will
change to the formatting you want.

- KC Rippstein
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:

Hi,
I have 10 sheet for inventory record of Paintings, Silver Jewllery,
Textiles, Wooden Items etc. I track cost price of entering its ID number by
using this this formula
=IF(A523="","",VLOOKUP(A523,INDIRECT(B523),10,0)*( 1-(50%*--(B523<"slvrJwllry_Articles")))) to track specific value from respecive sheet.

But I want to do more. Is there any way when I enter a IDnumber of
particular item, then in the respective sheet this item number's detail
automatically freeze and change its color.

It sounds to me like you really need to keep a transaction register. I would
suggest making a new sheet called "Transactions" and record all new
transactions in there every day. A1 is the title "Date", B1 is the title
"ID", C1 is "Qty", D1 is "Category", E1 "Description", F1 "Cost", G1
"Retail", H1 "Profit", I1 "Reference".

Whenever you add an item to inventory, put it on this sheet using a positive
number. Whenever you make a sale, put each item on this sheet using a
negative number. You only enter data into A, B, and C. D:I are formulas.

I would suggest using the Data - Create List function on this page. This
way your formulas will automatically be added to any new transactions and it
will auto-filter for you.

Also, you need to keep a master sheet called "Categories" that tells you
what worksheet a particular item is on. It should have all possible product
IDs in column A and the correct worksheet name in column B.

Now go back to your "Transactions" page and
D2 =IF(C2<0,VLOOKUP(B2,Categories!$A:$B,2,FALSE),"")
I2 =IF(C2<0,D2&"!$A:$D,"") which assumes that your inventory sheets are set
up with ID in A, Description in B, Cost in C, and Retail Price in D.
E2 =IF(C2<0,VLOOKUP(B2,INDIRECT(I2),2,FALSE),"")
F2 =IF(C2<0,VLOOKUP(B2,INDIRECT(I2),3,FALSE),"")
G2 =IF(C2<0,VLOOKUP(B2, INDIRECT(I2),4,FALSE),"")
H2 =G2-F2

Once your first two transactions are entered, highlight H2:H3 and give it a
name (like NetProfit). Insert a new row at the top of your "Transactions"
page for totals, G1 "Total", H1 =SUBTOTAL(9,NetProfit).

Then your inventory sheets can keep a running balance of your inventory
based on a SUMIF formula. Simply conditional format those sheets to change
the format to what you want when your inventory balance for any one item is
0. Let me know if you need help with that.

Now you can ditch the report you had and just use the filters on the
"Transactions" page to print a report for any day you want. Just put column
I outside the print area.

- KC Rippstein
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:


Thanks for response.
I wnat to do change color or put text "sold" in the particular item which
has sold.

For your better understand my case i want to give some more detail to you.

I have a workbook which have 5 worksheets. 4 worksheets haveinventory record
of different items and 1 worksheet for my daily sale report, its column is-

ID, Name of Item, CostPrice SalePrice, Loss/Gain

I use just to know gross profit of the day. After taking print out I delete
its data.

In inventory sheets i want to color change or putting text "sold" in
respective sheet permanantely using just after sale report data entered.

Is there any way.

Regards

"KC Rippstein" wrote:

I don't know what you mean by freezing the item (can you explain?), but
having it change colors is fairly easy.

Give cell A523 a name (like ItemIDQuery). Then on each product sheet,
select the entire data table and apply conditional formatting using Formula
Is.
=$A2=ItemIDQuery

Select the formatting you want and then whenever the item ID matches a
particular ID in column A of your product worksheet, that entire row will
change to the formatting you want.

- KC Rippstein
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:

Hi,
I have 10 sheet for inventory record of Paintings, Silver Jewllery,
Textiles, Wooden Items etc. I track cost price of entering its ID number by
using this this formula
=IF(A523="","",VLOOKUP(A523,INDIRECT(B523),10,0)*( 1-(50%*--(B523<"slvrJwllry_Articles")))) to track specific value from respecive sheet.

But I want to do more. Is there any way when I enter a IDnumber of
particular item, then in the respective sheet this item number's detail
automatically freeze and change its color.

Respected Sir,
Thank you very much for taking detail focus on my problem. I got a new idea
from
you reply. I copied all data from all sheets in the new sheet "Transactions".

The format look like this-
A1 B1 C1 D1 E1 F1 G1 H1 I1 J1
Date ID Qty Catgry1, Catgry2 Dscrptn Cost Retail Profit Reference
13.05.06 001 1 Paintings Paper Palace 500.00 1000.00
13.05.06 002 1 Paintings Paper Palace 500.00 1000.00
13.05.06 003 1 Paintings Paper Hunting 1000.00 1500.00
13.05.06 004 1 Paintings Silk Palace 500.00 1000.00
13.05.06 007 1 Paintings Cotton Lion 400.00 1000.00
13.05.06 010 1 SilverArtcl 0.140 Anklet 500.00 1000.00
13.05.06 011 1 SilverArtcl 0.040 Earings 500.00 1000.00
13.05.06 017 1 Textiles Cushion 700.00 1000.00
13.05.06 027 1 DaggerItems 500.00 0900.00

This is sample data of my new created "Transactions" sheet. Now please you
tell me how
to enter formulas in your suggested "Categories" sheet and what will be the
exact format
of this.

One thing that i want to ask you How to define ID of particular Category.

At present i have 11 sheets and if I copied it to "Transactions" it will
consume 16000 rows.
so also tell me that it is practical or not. Because when I tried to filter
my system goes
very slow.

Thanks and regards.

Respected Sir,
Thank you very much for taking detail focus on my problem. I got a new idea
from
you reply. I copied all data from all sheets in the new sheet "Transactions".

The format look like this-
A1 B1 C1 D1 E1 F1 G1 H1 I1 J1
Date ID Qty Catgry1, Catgry2 Dscrptn Cost Retail Profit Reference
13.05.06 001 1 Paintings Paper Palace 500.00 1000.00
13.05.06 002 1 Paintings Paper Palace 500.00 1000.00
13.05.06 003 1 Paintings Paper Hunting 1000.00 1500.00
13.05.06 004 1 Paintings Silk Palace 500.00 1000.00
13.05.06 007 1 Paintings Cotton Lion 400.00 1000.00
13.05.06 010 1 SilverArtcl 0.140 Anklet 500.00 1000.00
13.05.06 011 1 SilverArtcl 0.040 Earings 500.00 1000.00
13.05.06 017 1 Textiles Cushion 700.00 1000.00
13.05.06 027 1 DaggerItems 500.00 0900.00

This is sample data of my new created "Transactions" sheet. Now please you
tell me how
to enter formulas in your suggested "Categories" sheet and what will be the
exact format
of this.

One thing that i want to ask you How to define ID of particular Category. If
you can send sample file that will be good for me to understand.

At present i have 11 sheets and if I copied it to "Transactions" it will
consume 16000 rows.
so also tell me that it is practical or not. Because when I tried to filter
my system goes
very slow.

Thanks and regards.



"KC Rippstein" wrote:

It sounds to me like you really need to keep a transaction register. I would
suggest making a new sheet called "Transactions" and record all new
transactions in there every day. A1 is the title "Date", B1 is the title
"ID", C1 is "Qty", D1 is "Category", E1 "Description", F1 "Cost", G1
"Retail", H1 "Profit", I1 "Reference".

Whenever you add an item to inventory, put it on this sheet using a positive
number. Whenever you make a sale, put each item on this sheet using a
negative number. You only enter data into A, B, and C. D:I are formulas.

I would suggest using the Data - Create List function on this page. This
way your formulas will automatically be added to any new transactions and it
will auto-filter for you.

Also, you need to keep a master sheet called "Categories" that tells you
what worksheet a particular item is on. It should have all possible product
IDs in column A and the correct worksheet name in column B.

Now go back to your "Transactions" page and
D2 =IF(C2<0,VLOOKUP(B2,Categories!$A:$B,2,FALSE),"")
I2 =IF(C2<0,D2&"!$A:$D,"") which assumes that your inventory sheets are set
up with ID in A, Description in B, Cost in C, and Retail Price in D.
E2 =IF(C2<0,VLOOKUP(B2,INDIRECT(I2),2,FALSE),"")
F2 =IF(C2<0,VLOOKUP(B2,INDIRECT(I2),3,FALSE),"")
G2 =IF(C2<0,VLOOKUP(B2, INDIRECT(I2),4,FALSE),"")
H2 =G2-F2

Once your first two transactions are entered, highlight H2:H3 and give it a
name (like NetProfit). Insert a new row at the top of your "Transactions"
page for totals, G1 "Total", H1 =SUBTOTAL(9,NetProfit).

Then your inventory sheets can keep a running balance of your inventory
based on a SUMIF formula. Simply conditional format those sheets to change
the format to what you want when your inventory balance for any one item is
0. Let me know if you need help with that.

Now you can ditch the report you had and just use the filters on the
"Transactions" page to print a report for any day you want. Just put column
I outside the print area.

- KC Rippstein
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:


Thanks for response.
I wnat to do change color or put text "sold" in the particular item which
has sold.

For your better understand my case i want to give some more detail to you.

I have a workbook which have 5 worksheets. 4 worksheets haveinventory record
of different items and 1 worksheet for my daily sale report, its column is-

ID, Name of Item, CostPrice SalePrice, Loss/Gain

I use just to know gross profit of the day. After taking print out I delete
its data.

In inventory sheets i want to color change or putting text "sold" in
respective sheet permanantely using just after sale report data entered.

Is there any way.

Regards

"KC Rippstein" wrote:

I don't know what you mean by freezing the item (can you explain?), but
having it change colors is fairly easy.

Give cell A523 a name (like ItemIDQuery). Then on each product sheet,
select the entire data table and apply conditional formatting using Formula
Is.
=$A2=ItemIDQuery

Select the formatting you want and then whenever the item ID matches a
particular ID in column A of your product worksheet, that entire row will
change to the formatting you want.

- KC Rippstein
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:

Hi,
I have 10 sheet for inventory record of Paintings, Silver Jewllery,
Textiles, Wooden Items etc. I track cost price of entering its ID number by
using this this formula
=IF(A523="","",VLOOKUP(A523,INDIRECT(B523),10,0)*( 1-(50%*--(B523<"slvrJwllry_Articles")))) to track specific value from respecive sheet.

But I want to do more. Is there any way when I enter a IDnumber of
particular item, then in the respective sheet this item number's detail
automatically freeze and change its color.

The "Categories" worksheet just has product ID's in Column A and the
respective worksheet name in column B. To create it, you could copy column A
from A2 to the end of your list on each sheet and paste it to the
"Categories" sheet.

So if "Paintings" has 20 products, then you will paste in Categories!A2:A21.
Then highlight B2:B21 and type the word "Paintings" without quotes and hit
Ctrl+enter. The category name should match your tab name for each sheet.

Repeat this process for each inventory sheet you have until you are done.

As far as giving each product a unique ID, most companies assign a 6 to 8
digit SKU number to each inventory item. Most of the time, the first 2
digits reference what department or category the product fits into, then the
next two digits might be the year (to help keep the stock rotating), then the
last few digits will be whatever you want (like 0001, 0002, whatever).

You can certainly keep track of your inventory on their current inventory
worksheets. You just need to put a formula that keeps track of how much you
have in stock. For example, in E1 of each inventory sheet put "Stock Qty",
then highlight E2 to the end of your list and type the formula
=SUMIF(Transactions!B:B, A2, Transactions!C:C) and hit Ctrl+enter. Repeat
this process for each worksheet.

Your transactions page will be as long as you have number of sales times
number of days you are open. It may very well get to be 16,000 rows if you
are open 280 days per year and have 58 entries per day. This is precisely
why you should not use Excel as a database for such volumes of activity.
Access is much better suited if you have that many transactions per day. But
if you only have 20 sales a day on average, Excel can easily work with
filtering 5,000 rows for you. To make the filter easier on you as the year
goes on, you might want to set up an advanced filter and give yourself a
place to type in the date. Selecting the date you want from a very long
drop-down list is not efficient as the months go by.

Each January, commit an hour to archiving the year-end reports and then
start a fresh one for the new year. Just put the starting inventory for each
product, dated 12/31, in the Transaction register and your good to go for the
new year.

- KC
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:

Respected Sir,
Thank you very much for taking detail focus on my problem. I got a new idea
from
you reply. I copied all data from all sheets in the new sheet "Transactions".

The format look like this-
A1 B1 C1 D1 E1 F1 G1 H1 I1 J1
Date ID Qty Catgry1, Catgry2 Dscrptn Cost Retail Profit Reference
13.05.06 001 1 Paintings Paper Palace 500.00 1000.00
13.05.06 002 1 Paintings Paper Palace 500.00 1000.00
13.05.06 003 1 Paintings Paper Hunting 1000.00 1500.00
13.05.06 004 1 Paintings Silk Palace 500.00 1000.00
13.05.06 007 1 Paintings Cotton Lion 400.00 1000.00
13.05.06 010 1 SilverArtcl 0.140 Anklet 500.00 1000.00
13.05.06 011 1 SilverArtcl 0.040 Earings 500.00 1000.00
13.05.06 017 1 Textiles Cushion 700.00 1000.00
13.05.06 027 1 DaggerItems 500.00 0900.00

This is sample data of my new created "Transactions" sheet. Now please you
tell me how
to enter formulas in your suggested "Categories" sheet and what will be the
exact format
of this.

One thing that i want to ask you How to define ID of particular Category. If
you can send sample file that will be good for me to understand.

At present i have 11 sheets and if I copied it to "Transactions" it will
consume 16000 rows.
so also tell me that it is practical or not. Because when I tried to filter
my system goes
very slow.

Thanks and regards.



"KC Rippstein" wrote:

It sounds to me like you really need to keep a transaction register. I would
suggest making a new sheet called "Transactions" and record all new
transactions in there every day. A1 is the title "Date", B1 is the title
"ID", C1 is "Qty", D1 is "Category", E1 "Description", F1 "Cost", G1
"Retail", H1 "Profit", I1 "Reference".

Whenever you add an item to inventory, put it on this sheet using a positive
number. Whenever you make a sale, put each item on this sheet using a
negative number. You only enter data into A, B, and C. D:I are formulas.

I would suggest using the Data - Create List function on this page. This
way your formulas will automatically be added to any new transactions and it
will auto-filter for you.

Also, you need to keep a master sheet called "Categories" that tells you
what worksheet a particular item is on. It should have all possible product
IDs in column A and the correct worksheet name in column B.

Now go back to your "Transactions" page and
D2 =IF(C2<0,VLOOKUP(B2,Categories!$A:$B,2,FALSE),"")
I2 =IF(C2<0,D2&"!$A:$D,"") which assumes that your inventory sheets are set
up with ID in A, Description in B, Cost in C, and Retail Price in D.
E2 =IF(C2<0,VLOOKUP(B2,INDIRECT(I2),2,FALSE),"")
F2 =IF(C2<0,VLOOKUP(B2,INDIRECT(I2),3,FALSE),"")
G2 =IF(C2<0,VLOOKUP(B2, INDIRECT(I2),4,FALSE),"")
H2 =G2-F2

Once your first two transactions are entered, highlight H2:H3 and give it a
name (like NetProfit). Insert a new row at the top of your "Transactions"
page for totals, G1 "Total", H1 =SUBTOTAL(9,NetProfit).

Then your inventory sheets can keep a running balance of your inventory
based on a SUMIF formula. Simply conditional format those sheets to change
the format to what you want when your inventory balance for any one item is
0. Let me know if you need help with that.

Now you can ditch the report you had and just use the filters on the
"Transactions" page to print a report for any day you want. Just put column
I outside the print area.

- KC Rippstein
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:


Thanks for response.
I wnat to do change color or put text "sold" in the particular item which
has sold.

For your better understand my case i want to give some more detail to you.

I have a workbook which have 5 worksheets. 4 worksheets haveinventory record
of different items and 1 worksheet for my daily sale report, its column is-

ID, Name of Item, CostPrice SalePrice, Loss/Gain

I use just to know gross profit of the day. After taking print out I delete
its data.

In inventory sheets i want to color change or putting text "sold" in
respective sheet permanantely using just after sale report data entered.

Is there any way.

Regards

"KC Rippstein" wrote:

I don't know what you mean by freezing the item (can you explain?), but
having it change colors is fairly easy.

Give cell A523 a name (like ItemIDQuery). Then on each product sheet,
select the entire data table and apply conditional formatting using Formula
Is.
=$A2=ItemIDQuery

Select the formatting you want and then whenever the item ID matches a
particular ID in column A of your product worksheet, that entire row will
change to the formatting you want.

- KC Rippstein
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:

Hi,
I have 10 sheet for inventory record of Paintings, Silver Jewllery,
Textiles, Wooden Items etc. I track cost price of entering its ID number by
using this this formula
=IF(A523="","",VLOOKUP(A523,INDIRECT(B523),10,0)*( 1-(50%*--(B523<"slvrJwllry_Articles")))) to track specific value from respecive sheet.

But I want to do more. Is there any way when I enter a IDnumber of
particular item, then in the respective sheet this item number's detail
automatically freeze and change its color.

Sir,
I m trying to do according to you but It will very helpful for me that if
you can send sample file according to your idea.

Regards

Sir
Thank you very much. I not only have hope but full confidence that only this
platform can solve my problem.

I m totally confused after reading your 01/08/07 post. Please send me sample
file on your site link so that I could understand better.

Exactly I want to do that whatever ID No. I put in Sales Sheet, the related
row from Inventory sheet should be colored or automatically put a text €œsold€�
in anyone column which we want to give refer.

One thing more I want and it is very necessary that what item has sold it
should not be enter twice in sales sheet. How to do data validation for
double entry.

Your suggestion is good to use Access but dont have knowledge about that
and I have some familiar with Excel. So I want to do it in only Excel.

My Inventory Sheet Format is-
Date ID Category1 Category2 Descrption Qty.

My Sales repost for know what is Proft/loss occurred.
IDNo. Category1 Category2 CostPrice SalePrice Loss/Gain

Regards

Maybe something like this? Go to:
www.rippstein.groupfiles.com

- KC
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:

Sir
Thank you very much. I not only have hope but full confidence that only this
platform can solve my problem.

I m totally confused after reading your 01/08/07 post. Please send me sample
file on your site link so that I could understand better.

Exactly I want to do that whatever ID No. I put in Sales Sheet, the related
row from Inventory sheet should be colored or automatically put a text €œsold€�
in anyone column which we want to give refer.

One thing more I want and it is very necessary that what item has sold it
should not be enter twice in sales sheet. How to do data validation for
double entry.

Your suggestion is good to use Access but dont have knowledge about that
and I have some familiar with Excel. So I want to do it in only Excel.

My Inventory Sheet Format is-
Date ID Category1 Category2 Descrption Qty.

My Sales repost for know what is Proft/loss occurred.
IDNo. Category1 Category2 CostPrice SalePrice Loss/Gain

Regards

Sorry, it may be
rippstein.groupfiles.com
without the www
- KC
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:

Sir
Thank you very much. I not only have hope but full confidence that only this
platform can solve my problem.

I m totally confused after reading your 01/08/07 post. Please send me sample
file on your site link so that I could understand better.

Exactly I want to do that whatever ID No. I put in Sales Sheet, the related
row from Inventory sheet should be colored or automatically put a text €œsold€�
in anyone column which we want to give refer.

One thing more I want and it is very necessary that what item has sold it
should not be enter twice in sales sheet. How to do data validation for
double entry.

Your suggestion is good to use Access but dont have knowledge about that
and I have some familiar with Excel. So I want to do it in only Excel.

My Inventory Sheet Format is-
Date ID Category1 Category2 Descrption Qty.

My Sales repost for know what is Proft/loss occurred.
IDNo. Category1 Category2 CostPrice SalePrice Loss/Gain

Regards

Sir,
Thank you very much. Sir i m working with Handicraft showroom. Here is
every item have a unique ID. Because Its size, Weight and rate vary from
its previous.

So, How to handle it.

Please email your file to me at and I will take a look.
It sounds like your inventory pricing can change, so the transactions page
may need to be modified slightly.

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rao Ratan Singh" wrote:

Sir,
Thank you very much. Sir i m working with Handicraft showroom. Here is
every item have a unique ID. Because Its size, Weight and rate vary from
its previous.

So, How to handle it.