excel help

[jamesguy]

im trying to make an excel spread sheet but need a little help with a formula.

what im trying/wanting to do is get a total amount of a specific item.

lets say

worksheet 1
A1= the word "nails"
J1 = total amount sold

worksheet 2
A7= the word "nails"
J7 = total amount sold

basically i have 150 worksheets with total amounts and want to find out the easiest and best way to gather and add all the information of total "nails" sold onto one single line. There is another 100 lines per worksheet with other products and nothing is in the same order.

The only way i can think of doing it is by actually searching for each line that contains the word "nails" and adding them together.

e.g

=SUM('sheet 1'!J1+'sheet 2'!J7............etc x150 sheets x100 different items (not including any new sheets i want to add at a later date) = a lot of time consuming work.

what im looking for is something along the lines of

find all lines in column A that return the result of the text "nails" and then add all the totals of the corresponding J column together from all 150 worksheets.

any help would be gratefully accepted, i am not quite a novice and will understand what you say but cant get my head round this puzzle of mine.

thank you.

 

[Nanobyte]

Excel is not ideal for this sort of thing but it can be done just like you laid out. The big question is how you are going to do it. Ideally you would write an Excel VBA script. If you are looking for help in doing that, then you will need input from someone else! I detest VBA.

The alternative is to use one of the macro apps around. I use Macro Express and AutoIt. Macro Express is more user-friendly than AutoIt. AutoIt is more powerful and free. For Excel, AutoIt is the better option as it has a number of Excel UDFs (user-defined functions) which make the job easier. Learning to use either of these apps is highly useful for any automated function in most common programs. There is a learning curve though. You can combine the two - Macro Express can call AutoIt scripts.

Here's an outline in AutoIt (I'm not going write the program; this was more for my education)

1. Open workbook with _ExcelBookOpen
2. Define the text string you are looking for e.g. "nails" and column with the "amount" value being read
3. _ExcelSheetList will produce a list of worksheets by name and you can get a sheet count from the zero index value
4. Use _ExcelReadCell in a For loop to go down column and compare content with text variable, "nails". If you get say 2 to 8 empty cells in a row (depends on layout), assume end of worksheet. If "nails" found read numerical value in "amount" cell and exit For loop
5. Move "amount" into an array together with worksheet name
6. _ExcelSheetActivate to move to the next worksheet and loop back to 4.
7. Repeat until last worksheet done
8. Obtain total number of "nails" from array
9. Display/print amount array including final total
10. If another material count is needed, return to 2.
11. Final touch would be to add a new worksheet at the front as a summary using _ExcelSheetAddNew, _ExcelWriteArray. Save manually after checking or _ExcelBookSave .