This is really a cosmetic question. Excel by default starts counting from line one which is the header. Is there some way to have it start counting from the real line one (the line after the header). In the attached screenshot socks is row one but since the header is counted it has become row two.
Excel – How to get excel row numbers to skip the header
microsoft excelmicrosoft-excel-2003microsoft-excel-2007
Related Solutions
No. Omit the first row from your range when you auto filter. This way the auto filter buttons appear only on your bottom header row and the data gets filtered. I expect that right now your second header row is getting pulled into your 'data'.
You can't select a single cell and have excel figure this out. You have to select the range of cells you want excel to include.
Just as a warning to you and any other who attempt to do this:
Right now you should think about:
- how often will I use this per customer?
- how many customers are there?
- how will I input these Items?
- how many different Items are there?
These are the most important questions. I have done quite exactly, what you are trying to do, for a very small company, with aroud 3 receipts per month.
As always, please fast & cheap & excelOnly. However, nobody asked those questions above. Mainly because of cheap & excelOnly. And now, it is so obvious how much time is lost, whenever a receipt is searched, corrected, but mainly when the items are added. Not to mention how many different forms of the same item exists. Correcting this with excel is however possible, but IMHO, a real pain. Convinced the cutomer too, an EPR is now setup.
This is a database task. It is like one of the best examples of a database task!
Further, this might even be worth a small business ERP-System. Here in Germany, I would recommend you JTL-Wawi.
But, if this is too big of a jump, you should really think about using Access.
Some advantages:
- you can manage your customers
- you can manage your items
- you can move your dymanic behaviour to access
- you can manage your receipts easily
- you can still use excel as your output system
How this would work?
You would have some tables, for customers and items, and for receipts. The receipt table would connect a customer to his items and other receipt relevant attributes.
Then you could create a designed report for a basic receipt, and print it from access. Or use excel to select a receipt, get the data and print it. Because the data would be predetermined, you would know how many rows/cols are comming, and what their context is.
However - this wheel was already invented so many times, so you should look into the already existing ones ;)
Edit
A solution based on your behaviour request could use this attempt here:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = clngColumnRightToLastGrade Then
Application.EnableEvents = False
'offset selection, one row down, two cols to left
Target.EntireRow.Insert (xlShiftDown) 'insert new row
Target.Offset(1, -2).Select
Application.EnableEvents = True
End If
End Sub
You could of course use the Change
event instead and you might have to limit this behaviour to the maximum range of your dynamic list or to the worksheet of it.
Or just make a macro acceible per shortkey to execute this line here:
Target.EntireRow.Insert (xlShiftDown) 'insert new row
You will need to determine the currently selected cell to have the Target
range.
Best Answer
Simply put, the answer is no; that feature does not exist in Excel.
If you needed it for some reason, you could easily implement your own ID column and populate the cells with the numbers you want represented.
Like so:
Then, you simply removing the column and row headings. That way you get the illusion that the rows are starting with your data.
Just a thought.
-Justin