Forum Discussion
Solved
Excel formula help needed
I have two workbooks (separately saved) lets call them book1 and book2.
I need to have a cell in book1 pull the next available number (based on certain criteria) from book2. I am having a difficult time creating this successfully. I started with vlookup but since they are in different books completely it isn't working.
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'?
- For example
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 dateOpen 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.
