Excel Utility to Validate any GSTN Number- No VBA/No Macro

Dear Friends


Excel Utility to Validate any GSTN Number- No VBA/No Macro.. therefore you can freely use it and share through mails/gmail.

This is a free utility useful for CA Staff/Accounts professionals who have to deal with a lot of GST numbers while working on GST returns.. This file comes with built in formulas to validate the pattern of GST number as per GST portal specification.

In a nutshell it will alert you about the wrong GSTN number in your data, therefore we thought it worth sharing with you.

While working on it.. I learnt that MS Excel has got very astonishing functions...

Download it from here : https://drive.google.com/file/d/1dQLvx0pDig-vQjqTaM1yNBp7fYkzeGZ4/view?usp=sharing
[COLOR=rgba(0, 0, 0, 0.87)][/COLOR]

 

Thank You for Sharing a nice utility ..... Keep Sharing

 

would like to request you can anybody teach me about jasson file how it works and will it work in 9.1.1

 

JSON=Java Script Object Notation

In Tally This File Type Works on Simple Collection Or Objects.

Many Examples Are Available in Our Forum Please Search Them And Gain Knowledge.

Happy Learning/Coding.

 

I observed that you hidden Named Ranges
Do you know how to hide the table names from name manager?

By the way you are sharing utility for free but why hiding formulas
may be some one can learn from your experience
Especially that GSTIN checksum concept

 

Yes. Named Ranges are hidden, purpose is to avoid accidental deletion or improper changes to the formulas. Sum of the formulas are very scary
to the eyes of viewers. I broke them into a number of sub formulas but still very scary.. a lot of efforts have gone into them.

 

Do you know how to hide the table names from name manager?
I can hide normal named ranges but when I tried to hide table name I am getting error

 

I used the VBA macros to hide the named ranges and then deleted the VBA code from file


In Excel there is no other way but using VBA codes to deal with hiding or displaying all named ranges. You can copy the following codes to hide or unhide range names in your workbook.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA: Hide all named ranges in Excel

Code:

Sub HideNames()
'Update 20140318
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
    xName.Visible = False
Next
End Sub

VBA: Display all named ranges in Excel

Code:

Sub ShowNames()
'Update 20140318
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
    xName.Visible = True
Next

End Sub

 

I tried this method It works perfectly on normal named ranges
but I want to hide table names(its not working)

 

Dim rng As Range
Dim ars As Areas
Sub ToggleTable1ViewRows()
Set rng = Range("Table1").CurrentRegion
rng.EntireRow.Hidden = Not rng.EntireRow.Hidden
End Sub
Sub ToggleTable1ViewCols()
Set rng = Range("Table1").CurrentRegion
rng.EntireColumn.Hidden = Not rng.EntireColumn.Hidden
End Sub

 

You try this. you have already have a hidden sheet named "HideTable"

Sub ToggleTableHiddenSheet()
Const sHomeSheet = "Sheet1"
Dim sMoveToSheet As String

With Range("Table1[#All]")
If .Parent.Name = sHomeSheet Then
sMoveToSheet = "HideTable"
Else
sMoveToSheet = sHomeSheet
End If

.Cut Destination:= _
Sheets(sMoveToSheet).Range(.Cells(1).Address)
End With
End Sub

 

Tnq for taking time to solve query
But my problem is - I have 12 tables + 20 named rages total = 32 named ranges in named ranges list
Now I didn't want user to view all those 12 tables names in Named range list, but user should see Table and enter data in that Table

So your solution to move table to hidden sheet will not work as I need user to input data in Table

 

Does this do what you want?


Sub HideRows()
Dim StartRow As Long, EndRow As Long, lr As Long, i As Long
StartRow = 15
EndRow = 200
For i = StartRow To EndRow
If (2 = WorksheetFunction.CountIf(Range("I" & i & ":J" & i), 0)) And (3 = WorksheetFunction.CountIf(Range("O" & i & ":Q" & i), 0)) Then
Rows(i).EntireRow.Hidden = True
Else
End If
Next i
End Sub

 

This code Hides Rows means Table will be hidden
Table should be there(User Enters data)

 

Send ur excel file and what u want write down in mail... kissudears@gmail.com

 

I searched everywhere(so many days ago) didn't find any solution

I observed Garima hidden named ranges in his sheet so I asked here

 

Corrected

Already asked in several Forums, Groups related to c#, VBA and Excel, but no solution found
I will give a try in that forum also