Forum Discussion
Link expansion creates to many characters in cell formula
Hello,
I am using a sum-if-set formula (SUMMER.HVIS.SETT in norwegian) to access data in a excel file located in our network. When the file is open, the linking is short enough for the formula to give correct data, but when closed, the link path becomes so long that the result is #VALUE!
Is there at way, in excel, to make a fixed length on such links, like defining a variable?
The example can be (when closed linked file):
SUMMER.HVIS.SET('http://enhetsrom.bedrift.no/123456/Dokumenter/Rapportering/test (skjult)/Timer/[Alle timer per koststed.xlsx]Solgte timer per koststed'!$S:$S; 'http://enhetsrom.bedrift.no/123456/Dokumenter/Rapportering/test (skjult)/Timer/[Alle timer per koststed.xlsx]Solgte timer per koststed'!$F:$F;mine_prosjekter!D2)
Thus I would like to sum values in column S in the linked file for the criteria defined (may also contain column data to the linked file). So in the example above, how can the http link be shortened for this formula?
Regards!
3 Replies
SUMIF() doesn't work with closed file, both files are to be opened. You may use SUMPRODUCT() instead.
Thanks for the prompt reply!
This indeed works, but some of the compare in my data uses text that SUMIF() can cope with, but SUMPRODUCT() creates a unknown value in the array ({TRUE,TRUE,#VALUE!,FALSE,...}). Have to figure out a way to either remove the text cells, or find a way to use text (f.ex. my sample data may have 973010, 0389-30, 357892U as values to compare against, where both the latter are text).
THA_68 From this point of view I see no difference between SUMIF() and SUMPRODUCT(). To be more concrete it's better to see some sample.
