Excel/Need Macro


Order Sheet
Order Sheet  

Bill Sheet
Bill Sheet  
I am new to macro in excel. I have attached 2 image which is JPEG format. I need macro for this & I am using MS office excel 2007.

I developing any software.

Attachment contains :
1. Order Sheet
2. Bill Sheet (Contains 'n' number of companies)

My Question are as follows:

1. If I add the Items in Order sheet in Bill sheet the items should automitically should update along with Quantity.

2. I need Serial Numbers Column 'A' automatically from macro against Items in Column 'B'. The serial number should start from '1' with 'n' numbers for each bill sheet and in Column 'D' there is Unit, I use to enter manual (e, box, sets, reams, kgs, Grms, Dozs, Ltrs, No, Nos, pkts, if I enter these in order sheet at last column or between Total & Rate Column), but I need there macro & also in Column 'H' same (e, box, set, ream, kg, Grm, Doz, Ltr, No, No, pkt).

3. If I add RATE (F column Bill sheet) column in order sheet the amount should take automatically & in E column Rs. & G column /- should take. Even if I change the rate (amount) of the product it should update.

4. Instead of entering Rate (Amount) of product in bill sheet (i.e F column). If I insert Rate (Amount) of each product in order sheet after Total column (order sheet).
Example :

4a. In bill sheet E column Rs. & G column /- should automatically add. In macro the programme should be written. Coming to bill sheet RATE which is order sheet.
Example : Rs.50/- (Rs. In EColumn, Amount 50 (which I enter in order sheet rate column) in F Column & /- G Column) should update also In 'J' Column '00' should also update.

5. Bill No. which I enter in Red color Column 'G4'(Bill Sheet), which should start from "0001" or in middle I change the number "0239" should take continues next numbers for each sheet (Yes each sheet get a different number) with 'n' numbers. I have merged that column (means Column 'G' is small so that I have merged upto Column 'J' to look clearly).

Numbers should be updated when I enter the quantity in order sheet which has been given from the companies even if the quantity is blank the number should take to the next bill no. in bill sheet .

6. The bill no. which ends says 240 for the present month (November). If I SAVE AS the file for next month (December) the bill no. should take next contineous Bill No.241 like that it should take.

7. If Quantity is nil in order sheet (means blank) the Unit Column in bill sheets will be remains same, but it should be blank in bill sheets.

8. If add 'n' number of columns & rows (means company & item name) in order sheet & will add bill sheets (means company) it should update.

9. In 'I' Column of Bill sheet the amount should be total against Quantity "C' Column & Rate 'F' Column.

10. Total should take in macro itself which 'I38' column & also should be words which I mention in bill sheet.
I have mention the numbers in JPEG file against questions.

Order sheet & bill Sheet are in same excel file

Please help me in this about the macro & need.



Your questions are multiple (10), and very involved they would take hours to answer with clarity.  I suggest that you narrow down what you need to know after having attempted to create the solutions yourself, and reask the question.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Richard Roberts


Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.


Have been working with Excel for about 20 years primarily in accounting and financial areas.

BA, Certified Public Accountant

©2017 About.com. All rights reserved.