I've made the following changes to your file (mostly shown in blue):
a. Inserted a new column A in your three company sheets;
b. Put this formula in A2 of each sheet:
(copied down to row 10, i.e. beyond your data to allow for future expansion)
c. Put the value 0 in cell A1 of the ACME sheet (the first in the sequence)
d. Put this formula in A1 of the Global sheet:
e. Put this formula in A1 of the HSBC sheet:
(These formulae give a running sequence of records from one sheet to the next)
I then inserted a new sheet named Summary, and set up a small lookup table in columns I and J, where I2 downwards lists your sheet names in sequence, and J1 contains zero and J2 this formula:
which was then copied down to give the highest number in each sheet.
Cell A2 contains this formula:
to find the sheet name where the record numbered 1 exists.
B2 contains this formula:
which finds the row in the relevant sheet where the record numbered 1 occurs.
The other columns all contain INDEX formulae to bring the relevant data back from the appropriate sheet. For example, C2 contains this:
which gets the expiry date (column E) for the relevant record. Other formulae are very similar, the only change being to the E:E part. I've also added an Urgency column (G) to show you the ranking of the dates (earliest date = 1). The formulae in A2:G2 were then copied down to row 20.
So, just add a record to one of the company sheets and you will see it automatically transferred to the Summary sheet.
The columns in blue can be hidden if you prefer.
Hope this helps.
Pete
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks