Forum Discussion
Excel formula help needed
Open both workbooks.
Select the cell in Book2 where you want the next bid number.
Enter the formula
=INDEX('[Book1.xlsx]Bid Numbers'!$A:$A, COUNTA('[Book1.xlsx]Bid Numbers'!$B:$B)+1)
where Book1.xlsx is Book1, and Bid Numbers is the sheet with the bid numbers.
I have assumed that bid numbers are in column A and submitted dates in column B.
Press Enter. If all goes well, you should see the next available bid number.
Now switch to Book1.
Close it (saving it if necessary).
You'll note that Excel has added the path of Book1 to the formula, for example
=INDEX('C:\Excel\[Book1.xlsx]Bid Numbers'!$A:$A, COUNTA('C:\Excel\[Book1.xlsx]Bid Numbers'!$B:$B)+1)
Save the workbook with the formula.
Can you provide more detailed information?
- Which sheets are involved, and which ranges?
- What exactly do you mean by 'the next available number'?
book1 has:
BID # Submitted Date
2023001 9/1/2023
2023002
2023003
2023004
2023005
I want a single cell in book2 to pull the next available bid number that has no entry in submitted date
Open both workbooks.
Select the cell in Book2 where you want the next bid number.
Enter the formula
=INDEX('[Book1.xlsx]Bid Numbers'!$A:$A, COUNTA('[Book1.xlsx]Bid Numbers'!$B:$B)+1)
where Book1.xlsx is Book1, and Bid Numbers is the sheet with the bid numbers.
I have assumed that bid numbers are in column A and submitted dates in column B.
Press Enter. If all goes well, you should see the next available bid number.
Now switch to Book1.
Close it (saving it if necessary).
You'll note that Excel has added the path of Book1 to the formula, for example
=INDEX('C:\Excel\[Book1.xlsx]Bid Numbers'!$A:$A, COUNTA('C:\Excel\[Book1.xlsx]Bid Numbers'!$B:$B)+1)
Save the workbook with the formula.
- Thank you so much!