Excel - Search the Cell Name and read the value using jxl blirary
I have an excel sheet with is too lengthy. It has around 100 worksheets. All the worksheets have same kind of Column Names but the Values Differ.
I want to read couple of cell Values only from each worksheet and then store in it another Excel sheet.
I am able to count the Worksheets and as well as get their Sheet Name. But I am unable to search for my Cell Name and read it's value. Can you Please help.
Hi I suggest you adding one empty row in the first sheet of the workbook in the first row and have the count of excel sheets in A1 cell of that sheet. You can use the value of A1 cell to loop through.
Here are the details steps to get the number of sheets in excel using a formula:
1. Click 'Ctrl+F3', then 'Name Manager' Box Appears
2. Click 'New' (use shortcut key Alt+N)
3. Then, 'New name' box appears
4. Enter 'Name' as "CountSheets"(Your choice)
5. Enter 'Refer To' as =GET.WORKBOOK(1)&T(NOW())
6. Click OK
7. It takes you to the 'Name Manager' box again (you can find the Defined Name in the list)
8. Click 'Close'
9. Come to the Cell, where you want to enter the Formula
10. Enter the Formula as =COUNTA(INDEX(CountSheets,0))
11. Click Enter
If you google on this, you can find alternative solutions with VBA code. Without using VBA, I think this is the easy way to Count Number of Sheets.
Hope it was useful!!!
Aravindhan, Click here for QTP Tutorials