Referencing sheets with a sheet number as opposed to sheet name

[spawn2048]

Hello

I have a spreadsheet keeping track of inspection dates for a large quanitity of personal protective equipment (PPE) with each piece of PPE having it's own sheet containing all the details on all the inspections and then a sheet which is a summary of all pieces of PPE to make it easier to see which are in/out of date and need inspections.

All the different sheets have long winded names to identify which piece of PPE they refer to, and when referencing cells within sheets it is becoming a pain to type 'reallylongwindedname'.$B$1 each time and would much rather refer to them as 'sheet1'.$B$1. is this possible?

Would I then be able to drag them into a sequential list? I don't really know how to explain. Say if you have a column where A1=1, A2=2, A3=3 and you drag the bottom right hand corner 4,5,6,7,8 and so on is automatically inserted further down the column. I would like to achieve the same with referencing sheets. So I could type 'sheet1'.$B$1 in cell A1 and then drag downwards so that subsequent cells then read 'sheet2'.$B$1, 'sheet3'.$B$1, 'sheet4'.$B$1 and so on. Does this make sense? Is it possible?

Many thanks in advance.

[Prawny]

Without the use of VB, I don't think it's possible. I personally hate having more than 5 sheets in a workbook, so it's not really much of a problem for me.
The only thing I can think of is rename/abbreviate the sheets and have the full name of the PPE within that sheet.

[spawn2048]

What's VB?

[abousetta]

There is a way but it maybe more complicated that what you. Let me paste an example.

abousetta

vb is visual basic, vba is visual basic for applications, also known as macros (or a small program that can be customized and run in Office products). Its mainly used for automation or adding features that are not normally present in Office (e.g. User-defined functions)

[Dennis7849]

You might want to consider reorganizing the data. How much data pertains to one specific PPE? Can you share this workbook so I can make better recommendations?

[abousetta]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I created a table containing the sheet names and a number to reflect their order. In the formula I use LOOKUP to find the Sheet name that corresponds to the name I inputted in Cell D2. The formula is:

Please Login or Register  to view this content.

which would have been the same as writing this formula:

Please Login or Register  to view this content.