Assume a worksheet with formulas referring to the previous worksheet. When this sheet is copied by right clicking the sheet and selecting Move or Copy > Copy, formulas in this newly created sheet, should change to refer to the previous sheet. In other words, if sheet2 has formulas referring to sheet1, then when sheet3 is created (by […]
Read More →
When downloading data from an ERP into Excel, a Remarks column has entries which can be as lengthy as 300 characters per cell. Before uploading this data into another database, entries in the Remarks column need to be split into multiple cells of upto 50 characters per cell. Furthermore, when splitting data into multiple cells, it should […]
Read More →
{ 24 Comments }
Assume a stock purchase sheet which details the daily stock purchase of various Raw material at various per unit prices. On another sheet, the closing stock of each Raw material type is entered. In issuing Raw Material, one may want to follow the First In First Out (FIFO) method accounting which assumes the Raw material issued […]
Read More →
Assume data in range B4:B18 (heading is in cell B3). To compare the value in the current row with the value in the row below, one may enter the following formula in cell C4 and copy down =B4=B5 True’s indicate matching values with the value in the next row. However, the above formula would fail when […]
Read More →
{ 0 Comments }
In range B3:V3, one will record Purchase price of various Assets purchased in different months. In the next two rows one specifies whether the Assets purchased in that month have to be amortised or not and the months over which the Assets has to be amortised respectively. The task is to determine the amount to […]
Read More →
Assume there are two types of inverters – Inverter1 with a capacity of 15kW and Inverter2 with a capacity of 20kW. For an electricity load factor of, say 60kW, the objective is to determine the minimum number of inverters to be used. Therefore the number of inverters which can be used for a load factor of 60kW […]
Read More →
{ 5 Comments }
Assume data is in range A3:E10. In A4:A10, various service types are mentioned. In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012. In range B4:E10 are numbers representing number of customers. One may want to answer the following questions from this data: 1. For every date, total number of customer walkins and […]
Read More →
Assume a database where customers are listed from cell A6 down. From cell B5 to the right months are entered from April to March (B5:M5). In B6:M6 (Customer 1), a user enters 1’s and 0’s. A value of 1 respresents “Cheque bounced” and 0 represents “Cheque honoured”. Similar data is entered for other customers in B7:M500. […]
Read More →
{ 4 Comments }
Assume the following numbers in range C5:C22. Heading is in cell C4. Range C5:C7 – 11 Range C8:C11 – 14 Range C12:C13 – 23 Range C14-C19 – 56 Range C20-C22 – 78 One may want to colour cells for every change in number in range C5:C22 with the following two modifications: 1. Shading should be for […]
Read More →
{ 6 Comments }
Assume a five column database with the first column containing codes, second one containing a separator, third one containing some alpha values, fourth one is a combination of the first three columns and the last column has response times. One may want to create as many charts as there are codes in column A. The charts […]
Read More →
Worksheet formulas in a newly copied worksheet should point to the previous worksheet
{ 15 Comments }