Fix this excel formula

[Bird222]

This formula works.

=SUMIF('F:\FY2012\Test\[Invoice Summary FY12.xls]All Summary'!$E$5:$P$5,"<="&E$6,'F:\FY2012\Test\[Invoice Summary FY12.xls]All Summary'!$E$9:$P$9)

I am trying to replace the number "12" with a cell reference to D5. I tried this but it doesn't work

=SUMIF("'F:\FY20"&($D$5-1)&"\Test\[Invoice Summary FY"&($D$5-1)&".xls]All Summary'!"&$E$5:$P$5,"<="&E$6,"'F:\FY20"&($D$5-1)&"\Test\[Invoice Summary FY"&($D$5-1)&".xls]All Summary'!"&$E$9:$P$9)

Can someone show me the error of my ways?

 

[BoomerD]

Yes...

http://forums.anandtech.com/forumdisplay.php?f=10

 

[yuchai]

I think an "indirect" formula is needed somewhere in there. Look into that.

 

[Krazy4Real]

.999999999999999999999999999999999999999999999999

 

[JulesMaximus]

Your formula is pulling from a specific file and you are trying to change it to look for a different file name based on criteria in your active sheet?

 

[CPA]

Do you have several Invoice Summary tabs and you're trying to pull from the correct one based on some criteria?

 

[Bird222]

Your formula is pulling from a specific file and you are trying to change it to look for a different file name based on criteria in your active sheet?

Yes. I am just changing the year and it would be great just to change it in D5 and have the filename change.

 

[Tweak155]

Looks like he's actually trying to reference a workbook in a specific path based on what is in D5 and subtracting 1 from that value.

If you don't mean to subtract 1, what are you trying to do with $D$5-1? Otherwise get rid of the -1.

 

[DeviousTrap]

Is the filename with the corresponding year open when you're trying this or are you trying to get the formula to pull data from a workbook that's not open?

And with D5-1 are you trying to subtract 1 from the year? Check the formatting of that cell, if it's formatted as date that formula might not be doing what you want. (To test put =D5-1 in a new cell that's formatted as number and see what happens).

 

[Tweak155]

Try this:

=SUMIF('F:\FY20 &($D$5-1)& \Test\[Invoice Summary FY & ($D$5-1) & .xls]All Summary'!$E$5:$P$5,"<="&E$6,'F:\FY20 & ($D$5-1) & \Test\[Invoice Summary FY & ($D$5-1) & .xls]All Summary'!$E$9:$P$9)

 

[Bird222]

Try this:

=SUMIF('F:\FY20 &($D$5-1)& \Test\[Invoice Summary FY & ($D$5-1) & .xls]All Summary'!$E$5:$P$5,"<="&E$6,'F:\FY20 & ($D$5-1) & \Test\[Invoice Summary FY & ($D$5-1) & .xls]All Summary'!$E$9:$P$9)

^This doesn't work.

This works

=SUMIF(INDIRECT("'F:\FY20"&($D$5-1)&"\Test\[Invoice Summary FY"&($D$5-1)&".xls]All Summary'!$E$5:$P$5"),"<="&E$6,INDIRECT("'F:\FY20"&($D$5-1)&"\Test\[Invoice Summary FY"&($D$5-1)&".xls]All Summary'!$E$9:$P$9))

There are two problems with this however. 1. You have to have the file open to use INDIRECT. 2. Since my range is in quotes it is locked in. It would be ideal have the

"$E5:$P5"

portion be able to change rows as it is copied down. (I realize my formula above has the row locked ) Is there a way to keep the smiles from being substituted without using the

 tag?

 

[Tweak155]

^This doesn't work.

This works

=SUMIF(INDIRECT("'F:\FY20"&($D$5-1)&"\Test\[Invoice Summary FY"&($D$5-1)&".xls]All Summary'!$E$5:$P$5"),"<="&E$6,INDIRECT("'F:\FY20"&($D$5-1)&"\Test\[Invoice Summary FY"&($D$5-1)&".xls]All Summary'!$E$9:$P$9))

There are two problems with this however. 1. You have to have the file open to use INDIRECT. 2. Since my range is in quotes it is locked in. It would be ideal have the

"$E5:$P5"

portion be able to change rows as it is copied down. (I realize my formula above has the row locked ) Is there a way to keep the smiles from being substituted without using the

 tag?[/QUOTE]

Huh, worked on my small test so not sure. Really you should be doing this using VBA probably. Especially if eventually you want more than one value out of those workbooks...

 

[Bird222]

Huh, worked on my small test so not sure. Really you should be doing this using VBA probably. Especially if eventually you want more than one value out of those workbooks...

Are the spaces necessary? I'll try it again.