Jul 19 2023 09:04 AM - edited Jul 19 2023 09:05 AM
I am currently working on a spreadsheet that has multiple pages in the workbook. On the home page I have been asked to add a cell that automatically updates with the next available number in the sequence.
For example, if the number 2025 gets used in the workbook the coding would 'read' that and update the cell on the home page to read 2026 as the next available number.
Is there any way to do this without a lot of complicated code as I am not very good at that side on excel.
Jul 19 2023 09:13 AM
Let's say the numbers are entered in column A on a sheet named Data Sheet.
Enter the following formula in a cell on the 'home' sheet:
=MAX('Data Sheet'!A:A)+1
Jul 19 2023 09:21 AM - edited Jul 19 2023 09:22 AM
Since you said:
"multiple pages in the workbook"
and
"gets used in the workbook"
You may want to consider a 3D reference:
=MAX(Sheet1:Sheet3!A:A)+1
This requires the Sheets 1 and 3 and any sheets between them are used in the same way. It will get the maximum value anywhere in column A on sheet1 or sheet3 or any sheet between sheet1 and sheet3.
For more detail, read this.
If your sheets to be checked aren't next to each other, then you can consider HSTACK. For example:
=MAX(HSTACK(Sheet1!A:A,Sheet3!A:A,Sheet5!A:A)+1