Thank you for reading.
I started wanting to set up a report to in excel to go find data for a month period (single cell), quarter to date period (multiple cells) and year to date period (multiple cells).
Using curly braces in the VLOOKUP helped = sumproduct(vlookup(a1,'sheet1'!a1:z200, {1,2,3} ,0) .
This works great when you have a certain set scenario {1,2,3} . You can also have a limited number of scenarios and use if statements for the next scenarios (this worked for 12 months).
I am trying to lookup more columns now (over 60) based on a date and period of inquiry (month, quarter, year). I cannot use over 60 if, then statements.
I tried to add a cell reference or vlookup formula in the curly braces above, but neither worked. I am guessing they were not read or they did not return the correct information for the curly braces.
ex: if "Quarter to Date", the sumproduct'
vlookup the product name , on the worksheet tab (combined name from a cell reference),
vlookup the value of the {data } , by what the date of the period was (x5) from the control worksheet list,
this would in theory return what cell the sum based on the parameters and give the { data }.
The vlookup will return the correct group:
1
1,2,3
1,2,3,4,5,6
etc.
But, the data returned by the vlookup was not recognized by the curly braces { data}.
This resulted in a reference error.
=IF(X$4="Quarter to Date",sumproduct(vlookup(B8,indirect("'#,"&X$1&",#'!"&"$A:$CC"),{(VLOOKUP(X$5,control!$V$132:$Z$211,3,0))},0)),"test")
I tried other ways:
'=IF(X$4="Month to Date",sumproduct(vlookup(B8,indirect("'#,"&X$1&",#'!"&"$A:$CC"),{X6},0)),"test")
=SUMPRODUCT(VLOOKUP(B8,INDIRECT("'#,"&X$1&",#'!"&"$A:$CC"),{INDEX(control!$V$133:$AA$204,MATCH(X5,control!$V$133:$V$204,0),4)},0))
{concatenate(...)} - concatenate the data to make sure it was text
Basically, If I could lookup inside the { } , then I could return the columns I wanted to sum together. these columns are even laid out in a table on the control tab as guide. If I could have this VLOOKUP in the { curly braces}, it would work.
In theory..
I appreciate your help.
I don't have the images hosted to link a URL , but I can email them.
Thank you,
Jeremy
I started wanting to set up a report to in excel to go find data for a month period (single cell), quarter to date period (multiple cells) and year to date period (multiple cells).
Using curly braces in the VLOOKUP helped = sumproduct(vlookup(a1,'sheet1'!a1:z200, {1,2,3} ,0) .
This works great when you have a certain set scenario {1,2,3} . You can also have a limited number of scenarios and use if statements for the next scenarios (this worked for 12 months).
I am trying to lookup more columns now (over 60) based on a date and period of inquiry (month, quarter, year). I cannot use over 60 if, then statements.
I tried to add a cell reference or vlookup formula in the curly braces above, but neither worked. I am guessing they were not read or they did not return the correct information for the curly braces.
ex: if "Quarter to Date", the sumproduct'
vlookup the product name , on the worksheet tab (combined name from a cell reference),
vlookup the value of the {data } , by what the date of the period was (x5) from the control worksheet list,
this would in theory return what cell the sum based on the parameters and give the { data }.
The vlookup will return the correct group:
1
1,2,3
1,2,3,4,5,6
etc.
But, the data returned by the vlookup was not recognized by the curly braces { data}.
This resulted in a reference error.
=IF(X$4="Quarter to Date",sumproduct(vlookup(B8,indirect("'#,"&X$1&",#'!"&"$A:$CC"),{(VLOOKUP(X$5,control!$V$132:$Z$211,3,0))},0)),"test")
I tried other ways:
'=IF(X$4="Month to Date",sumproduct(vlookup(B8,indirect("'#,"&X$1&",#'!"&"$A:$CC"),{X6},0)),"test")
=SUMPRODUCT(VLOOKUP(B8,INDIRECT("'#,"&X$1&",#'!"&"$A:$CC"),{INDEX(control!$V$133:$AA$204,MATCH(X5,control!$V$133:$V$204,0),4)},0))
{concatenate(...)} - concatenate the data to make sure it was text
Basically, If I could lookup inside the { } , then I could return the columns I wanted to sum together. these columns are even laid out in a table on the control tab as guide. If I could have this VLOOKUP in the { curly braces}, it would work.
In theory..
I appreciate your help.
I don't have the images hosted to link a URL , but I can email them.
Thank you,
Jeremy
