MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help to refer a cell

  Asked By: Darcy    Date: Feb 22    Category: MS Office    Views: 1563

I need a help from you. I have a work sheet has six coloumns. Date,
product, Op stock, In, Out and closing stock. I have nearly 50 items
and out of them only about 20 will be IN and/or OUT. If I have an
item's closing stock about a week back and start again, I want a
formula which look at the closing stock of a particular product and
refer the previous closing stock to be copied on the present op stock.



6 Answers Found

Answer #1    Answered By: Jean Bell     Answered On: Feb 22

This is a hard one to follow.
since we don't know your acronyms
I'll try to summarize:
you have (6) Columns:
A1) Date
B1) Product
C1) Op Stock
D1) In
E1) Out
F1) Closing Stock

It sounds like what you're wanting is to set the Op Stock (column C)
to the value of Closing Stock, and clear the values in In and Out.
I take it that Closing Stock is a formula that is (Op Stock) + IN - Out ??

If this is what you're asking, it's pretty simple.
If it is not, then you'll have to do a better job describing what you need.

Answer #2    Answered By: Dominic Murphy     Answered On: Feb 22

Thanks for offering me the help. To explain more I have attached the file.
Kindly help  me out. If you could code me with VBA it will be better.

Answer #3    Answered By: Jeffrey Washington     Answered On: Feb 22

I didn't see anyone offer a vba solution so I did a quick and dirty
code under the "Workbook_SheetSelectionChange" sub to carry the last
closing stock  down to the "current" row.

Here's the logic:
-> click on any cell  on the "Opening Stock" column and if there
exist a previous  item matching the current cell, then it will be
carried down to the activecell. It will also throw in the formula
for the "closing stock" just incase it got deleted...

I didn't know all the specifics, so no error checking is done. You
will have to modify the code to suite your needs.

Download the xls file from here:

Answer #4    Answered By: Landra Schmidt     Answered On: Feb 22

Thank you very much for the Help. But still I have a problem. I have
nearly 50 items, where as the formula is working only if the items
are between a to Z. i.e 26 items. I need to have the actual name of
the items up to 50. I could see the code you put the string to be
referred as a-z. How to change it to refer  to the actual list of
items which is up to 50?

I hope you could under stand my problem. Could you help  me down

Answer #5    Answered By: Alexander Bouchard     Answered On: Feb 22

Assuming your product  names start  with letters a-z, just add an
asterisk to the end of the code like this "[a-z]*" or the entire if
statement should look like this:

If aRow > [opening_stock].Column + 1 And strCurrentItem <> "" And
strCurrentItem Like "[a-z]*" Then

If any of your product names start with a number, then you have to
change the above pattern to reflect the product names.

The other option is to just eliminate the check completely like this
(assuming if the name field is not empty, then it must be legit):

If aRow > [opening_stock].Column + 1 And strCurrentItem <> "" Then

Answer #6    Answered By: Erika Evans     Answered On: Feb 22

But What if instead of a - z couldn't it just be changed to a - yy as that
would lead to a longer list?

Just trying to help.

On a side note what about using an array?

Didn't find what you were looking for? Find more on Help to refer a cell Or get search suggestion and latest updates.