Excel ? add Sequential number to auto number receipts uniquely& sequentially against value.?
Hi
I’m doing a bit of book keeping and in order to present my accounts I would like to be able stamp my receipts with a unique sequential generated number in my excel sheet.
So for example
if row A1 is the sequential number and B1 is the value
when it comes to entering the next value B2, the cell A1 is automatically generated with a new number with what ever number I started off with in A1.
I’ve found a really good formulae for this
IF(LEN(B2) > 0,OFFSET(A2,-1,0)+1, ” ” )
but is there any way the formulae would know what cell it is in automatically so I didn’t have to copy and paste the formulae as a block into the whole column without having to go into each cell I wish for an automatic number and put its appropriate cell number.
Thank you for your help.
Gallahad
Are you asking how to copy the formula down column A so that each copied formula references the cell just above it and the the cell just to its right? If this is the case, there is a very easy way to do this.
Put your formula in A2
Click on the cell A2 and it will have a black highlight boarder around it
In the lower right corner of that highlight boarder is a small square.
Put your cursor over the small square and click+drag down to the other cells you want the formula copied to.
When the formula is copied, it will automatically offset the cell references. Try it.
Also, you could use this formula in A2 to do the same thing as your formula…
=IF(B2<>“”,A1+1,”")
Translation: If B2 isn’t empty then A2=A1+1 else A2 = nothing.
For future reference: this doesn’t apply to your case, but if you had a formula that you wanted to copy to other cells and the formula referenced two other cells and you wanted the formula copies to offset only one of the references but the other reference remain fixed, you put “$” signs in the cell reference like this…
=IF(B2<>“”,$A$1+1,”")
Drag/copy will offset B2 but not A1. In this case, A1 is called an absolute reference and B2 is called a relative reference.
Hope I understood your question correctly.
|
|
Counterparty Credit Risk: The New Challenge for Global Financial Markets (The Wiley Finance Series) £26.40 … |
|
|
Exploring Corporate Strategy: Text and Cases £40.99 … |
|
|
The Little Book That Builds Wealth: The Knock-out Formula for Finding Great Investments (Little Books. Big Profits) £6.56 In The Little Book That Builds Wealth, author Pat Dorsey-the Director of Equity Research for leading independent investment research provider Morningstar, Inc…. |
|
|
The Little Book of Value Investing $17.47 The Little Book of Value Investing offers investors (professional and amateur alike) the necessary tools to follow a value-investment model that consistently beats the market…. |
|
|
The Little Book of Value Investing (Unabridged) $13.09 The Little Book of Value Investing offers investors (professional and amateur alike) the necessary tools to follow a value-investment model that consistently beats the market…. |
|
|
Venus In Copper: Marcus Didius Falco, Book 3 $9.19 In the heyday of the Roman Empire, a small accounting error has left Marcus Didius Falco sharing a cell with a large rat…. |
|
|
Radically Simple Accounting: A Way out of the Dark and Into the Profit $17.47 Accounting is most definitely a unique language. So why not learn it like you would any other language – by listening…. |
|
|
Under the Green Star: Green Star, Book 1 $14.95 On Earth, life held for him only the fate of a recluse – until he found the formula that let him cross space to the world of the Green Star…. |
|
|
An Empirical Analysis of Stock Option Valuation Methodologies in Closely Held U.S. Corporations $23.07 New – The introduction of fair value accounting for stock options has required private companies to apply stock option valuation methodologies that were designed to be applied to their public counterparts. The two recommended methodologies, the Black-Scholes formula and the Binomial Lattice model, require the valuator to provide an input for estimated volatility; for private companies that do not have a trading history there is limited guidance regarding the determination of volatility, which re |
|
|
An Empirical Analysis of Stock Option Valuation Methodologies in Closely Held U.S. Corporations $23.07 Used – The introduction of fair value accounting for stock options has required private companies to apply stock option valuation methodologies that were designed to be applied to their public counterparts. The two recommended methodologies, the Black-Scholes formula and the Binomial Lattice model, require the valuator to provide an input for estimated volatility; for private companies that do not have a trading history there is limited guidance regarding the determination of volatility, which r |
|
|
An Empirical Analysis of Stock Option Valuation Methodologies in Closely Held U.S. Corporations $28.98 New – The introduction of fair value accounting for stock options has required private companies to apply stock option valuation methodologies that were designed to be applied to their public counterparts. The two recommended methodologies, the Black-Scholes formula and the Binomial Lattice model, require the valuator to provide an input for estimated volatility; for private companies that do not have a trading history there is limited guidance regarding the determination of volatility, which re |
|
|
An Empirical Analysis of Stock Option Valuation Methodologies in Closely Held U.S. Corporations $28.98 Used – The introduction of fair value accounting for stock options has required private companies to apply stock option valuation methodologies that were designed to be applied to their public counterparts. The two recommended methodologies, the Black-Scholes formula and the Binomial Lattice model, require the valuator to provide an input for estimated volatility; for private companies that do not have a trading history there is limited guidance regarding the determination of volatility, which r |