LinkBack URL
About LinkBacks
Fix form: Find unique Values and Sum using array and INDIRECT for sheet reference in Cell.
Hi I am new to this forum and would like to ask for some help.
I am currently having a sheet sent from MS Access to an excel document, this means that the sheet added from access has to be referenced INDIRECT since it does not exist originally.
I have completed this for 2 other ARRAY formulas but cant seem to get it to work two others.
Working formula 1 (2 criteria):
{=SUMPRODUCT(((INDIRECT("'"&N1&"'!$K$2:$K$65000")=B1))*((INDIRECT("'"&N1&"'!$J$2:$J$65000")>G2)))}
N1 is sheet name reference. This collects (counts) all data based on the criteria in B1 and G2 in the sheet referenced in N1
Working formula 2:
{=COUNTIF(INDIRECT("'"&N1&"'!$J$2:$J$65000");">"&G2)}
This counts all data over the value in G2 in the sheet referenced in N1.
NOT working formula1:(located in A3,A4,A5 etc)
{=INDEX('Sheet A'!$K$3:$K900;MATCH(0;COUNTIF($A$2:A2;'Sheet A'!$K$3:$K900);0))}
This formula works as it is now where it gathers all unique values in column K. However I can seem to get it to work with INDIRECT, made several attempts, dont think I should paste them since they dont work. In other words what I would like is to replace 'Sheet A' with a reference to N1 where the name of sheet is located, right now it is Sheet A but I need to able to change the cell value at will in order to reference other sheets.
NOT working formula2:
{=SUM(IF('Sheet A'!$K2:K10000=B1;IF('Sheet A'!$J2:J10000>G2;'Sheet A'!$W2:W10000;0);0))}
This formula sums the values in W where the criterias in B1 and G2 is fulfilled. Would like to exchange 'Sheet A' for a INDIRECT reference and retrieve sheet name from N1.Is this possible? I mean it should be but I cant seem to figure out how, it drives me crazy. Any help would be much appreciated.
Please feel free to as if nothing makes sense, since I tend to ramble
Register To Reply
icon below to give reputation feedback, it is appreciated.
Bookmarks