Write Excel macro for a spreadsheet we will provide to work out the last row of data in a number of tabs and then update details of that row in formulae on the main reporting tab of the spreadsheet. Copy updated formulae across cells of the reporting tab. Sort data in one of the tabs in a certain sequence.
Please provide a fixed cost quotation for this project, and an estimate of when you could complete the work.
In detail:
1. Find the bottom data row in all reference table arrays - ie. In the following tabs - Stock on Hand Figures - Melbourn, Stock on Hand Figures - Sydney, Demand by Warehouse, Daily Usage Figures, Daily Usage Raw, Incoming Stock
2. UPDATE FORMULAE IN SETUP AREA (Rows 8 to 24 in - Forecast Shortages tab - cells C8:J8, B9:AE24) to ensure that all formulae reference the full data arrays (ie. All rows of reference data identified in step 1 above on each tab)
eg. IFERROR((VLOOKUP($A9,'Stock on Hand Figures - Sydney'!$A$4:$D$100,4,FALSE)),0)
In this case, $D$100 is changed to always be the last row of the relevant reference data tab
**Only applies to numbers that are after the colon (ie. The end row/cell reference of the array).
3. Reset All rows in "Forecast Shortages" as follows
Copy SETUP AREA rows and paste in to all DATA ROWS (ie. Rows below Row 25 in the Forecast Shortages tab)
Table C8:J8 must be pasted next to the product description.
Table B9:AE24 must be pasted in cell underneath product description.
4. Sort "Incoming Stock" Tab rows by Column I ascending and in alphabetical order.
We will provide the excel file once the project is awarded