I have an inventory spreadsheet with each chest having its own sheet with the sheet numbered and each of those sheets have identical syntax. Rows 1-4 are protected by the separator line, so data starts from row 5:
A|B|C|D|E|F|G|H
<slot number or nothing>|ITEM NAME|TOTAL COUNT OF ITEMS IN ONE STACK|VALUE OF 1 OF THE ITEMS IN COPPERS|TOTAL VALUE OF THE ITEMS IN COPPERS|<...> SILVERS|<...> GOLDS|<...> PLATINUMS
1|clay block|119|0|0|0|0|0
2|tin ore|99|75|7425|74.25|0.7425|0.007425
There's a sheet named -ALL ITEMS-
, same syntax as the numbered sheets, which is supposed to make calculation of total counts of each item, for example iron ore
. I already tried but couldn't figure out how to check each numbered sheet for the item and add up its count, in C column, to the C column in the -ALL ITEMS-
sheet.
My attempt had 2 problems: if a sheet didn't have iron ore
, it returned #N/A
, and even if there was a row, it returned just that 1 row's TOTAL COUNT OF ITEMS IN THE STACK
, not all of them if there was more than 1.
Dunno if you wanna see my code, but I show it just in case. These were in -ALL ITEMS-
's C column:
=INDEX('1'!C5:C9999, MATCH("iron ore", '1'!B5:B9999, 1), 0)
=335
=INDEX('1'!C5:C9999, MATCH("iron ore", '1'!B5:B9999, 0), 0)
=99
=INDEX('4'!C5:C9999, MATCH("iron ore", '4'!B5:B9999, 0), 0)
=99
[because sheets 1 and 4 have iron ore
, and 4 has it twice with different TOTAL COUNT IN STACK
for each]
=SUM( INDEX('1'!C5:C9999, MATCH("iron ore", '2'!B5:B9999, 0), 0)+INDEX('2'!C5:C9999, MATCH("iron ore", '2'!B5:B9999, 0), 0)+INDEX('3'!C5:C9999, MATCH("iron ore", '3'!B5:B9999, 0), 0)+INDEX('4'!C5:C9999, MATCH("iron ore", '4'!B5:B9999, 0), 0) )
=#N/A
, error: Did not find value iron ore
EDIT 0: It'd speed up things very much if the item name would be auto-taken from the B column to the left instead of having to be specified in the commands, especially since there are -many- chests and more are coming steadily.
-ALL SHEETS-
tab, link after this, it has those formulas written and I wroteiron ore
to each so that it's not forgotten what item is in question. docs.google.com/spreadsheet/…