• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation

i mean how restrict the date field mistake in any one the column

19.04.2012 this should be enter as date format 19-04-2012, 19/04/2012 or 19/04/12


and


how to restrict the vehicle Nos. in a cell like


OR10G/8731 IT MUST be ENTRY LIKE THIS OR 10 - G / 8731
 
The date field is easier. Data Validation - Data, Date after (choose date).


I'll have to think some more about the latter.
 
Not sure how much restriction you want for the latter, but here's a start.

Data Validation, Custom formula:

=AND(MID(A2,3,1)=" ",MID(A2,6,3)=" - ",MID(A2,10,3)=" / ")

Other possible things to add to the AND function would be checking for text/numbers at certain spots, but I'm not sure what all the "hard" rules are w/o more examples.
 
THANK Q SIR


AND I WANT TO KNOW MORE FOR DATE


WILL YOU


and i think the custom formula is not working did you try that formula which you was suggest for me
 
Hi, sgmpatnaik!


Would you please clarify your last post? Try elaborating it a bit more, and consider uploading a sample file so as to make people easier to help you.


Include details about what did you mean with "know more about date" and why did that custom formula didn't work for your vehicle numbers. It works for the only example you provided: 3rd char space, 6th-8th chars space dash space and 10th-12th chars space slash space. If that doesn't work for you, let's see your actual data.


Regards!
 
Hi, sgmpatnaik!


Give a look at this file:

http://dl.dropbox.com/u/60558749/Data%20Validation%20-%20Sample%20%28for%20sgmpatnaik%20at%20chandoo.org%29.xls


It has data validation for highlighted cells. Copy down as needed.


Regards!
 
Ah Thank Q So Much sir exact what i am thinking


sir i need some help about vlookup and index


i mean suppose i maintain the stock receipt, stock dispatch then automatically stock book will be generate as like


Sheet1- Stock Receipt


CellA CellB CellC CellD CellE

Row 1 02.04.12 01 orl - / 1981 200 PPC

Row 2 02.04.12 02 orl - / 1982 300 ppc

Row 3 03.04.12 03 oss - / 1983 250 opc


Sheet2- Stock Dispatch


CellA CellB CellC CellD CellE

Row 1 02.04.12 01 orl - / 1981 200 PPC

Row 2 03.04.12 03 oss - / 1983 100 opc


Then Automatically Stock Book Generate in Sheet3


Sheet3- Stock Book For PPC


CellA CellB CellC CellD CellE CellF

Date O.B Receive Total Sales Balance

Row 1 02.04.12 500 500 200 300

Row 2 03.04.12 300 - 300 - 300


Sheet4- Stock Book For OPC


CellA CellB CellC CellD CellE CellF

Date O.B Receive Total Sales Balance

Row 1 03.04.12 250 250 100 150

Row 2 04.04.12 150 - 150 - 150


Is it possible to Maintain
 
Hi, sgmpatnaik!

I only understood that my previous post was what you were thinking on. First line. Line 2 readable too, but from line 3 to the end, I missed everything.

Can you please upload a sample file with examples, input, formulas and desired output? Include too usage procedures.

Regards!
 
Here is the link for above question


https://www.dropbox.com/sh/wu2txjinvyy2qxq/Ki9Y8E3Q7X/Account.xls


please suggest me
 
Hi, sgmpatnaik!


I've seen your uploaded file and it seems to be more an incomplete specification's document to explain what I told you I didn't understand more than a sample file that you have build up to a point where you faced an issue and you were asking for an specific doubt here at this forums.


If what you need to build is a system that let's you manage Stock, Sales, etc., well I think that this isn't neither the adequate place nor the used form for such a requirement.


People here can help you with questions, formulas, small pieces of code, but as far as I know not about building a Stock&Sale(&Buy too maybe?) system in Excel. It would be a far different scenario if you arrived to a situation where you don't know how to implement a certain feature and you upload your developed workbook so as to might be able to guide you.


Consider elaborating your uploaded file to an extent where what you're asking for it's a question or a guideline but not a full consultant or programming service.


Regards!
 
Hello Sir,


Kindly suggest me about the validation


i made some data validation in sheet1 per suppose


Ex for Date:


Data>validation>Allow>Date>Data>Between>Start Date>01-01-1900>End Date>31-12-2100

Then Ok


i did the validation for date field in sheet1 of cellA


and i copy the Date field from Sheet1 to Sheet2


when i did the filter option in sheet2 then it's displaying in the filter option as

Select All

2012

1900


But when i did in filter option in sheet1 it's displaying normally as like

Select All

2012

Blanks


Kindly suggest
 
Sir, Please check my work book and suggest me


as per your request i submit my file for your kind information


http://dl.dropbox.com/u/75654703/Toshali%20Cements%20Accounts.xls


the problem between the date field in Stock Receipt and Receive Register


for your help i explain the below:


1. in Sheet2 press the Stock Receipt and Dispatch check the two Date fields are in there


2. in Sheet2 press the Receive Register here also two date fields are there which is Auto Copping from Stock Receipt and Dispatch to Receive Register
 
Hi, sgmpatnaik!


Do this:

a) In sheet 'Receive Register' eliminate data validation for columns B and D, as there is no input in those columns (data validation should take place where input occurs, not where inputted cells are referenced)

b) References to empty cells retrieves a 0 (zero) value, that's why you get 2012 and blank in input sheet and 2012 and 1900 (date 0 for 01/01/1900) in referenced sheet

c) If you want to have the same 2012 and blank replace formula for cell B5 in sheet 'Receive Register' from:

='Stock Receipt and Despatch'!B5)

to:

=SI(ESBLANCO('Stock Receipt and Despatch'!B5);"";'Stock Receipt and Despatch'!B5) -----> in english: =IF(ISBLANK('Stock Receipt and Despatch'!B5),"",'Stock Receipt and Despatch'!B5)

d) copy B5 to B5:B1504 and D5:D1504


Regards!
 
Ah Thank q So Much Sir


one more request how can i copy the data from Receive Register and Despatch Register


i mean from receiver register copy the Cell b, Cell G and Cell H and from Despatch Register Cell B, Cell J and Cell L to One sheet
 
Hi, sgmpatnaik!


I actually don't understand you at the first time. English is not my native language (it's spanish) so perhaps I'm not as flexible and permeable to some idioms as required.


If I read you in plain text, I should say "copy & paste when needed", but I guess that won't help you. So I suggest you to consider the following points when requesting for help:

a) specify what should be done, with what source data and to what target destiny

b) specify if it should be done with formulas, manually once, manually repeatedly, automatically, and in this case triggered by what conditions (Excel events, user actions, combination of cells, ...)

c) specify which interaction is expected from the user (wait for task completion, click on a button, run macro manually, perform checks at the end, ...)

d) provide an example starting from the existing data and entering manually the desired output

d) check carefully that all the information provided in your example is totally enough for understanding what you're asking for and for building the solution, even if the other person doesn't know anything but your post and your uploaded file: which in fact is the truth!


Otherwise, it's very difficult to use the time efficiently in order to accomplish your issues and try to help you, at least for me. We're having this kind of messages far more frequently than the average.


Regards!
 
You must log in or register to reply here.
Back
Top