• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Fix this excel formula

B

Bird222

Diamond Member
This formula works.

Code: 
=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

Code: 
=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? 🙂
 
Last edited by a moderator:
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?
 
Do you have several Invoice Summary tabs and you're trying to pull from the correct one based on some criteria?
 
JulesMaximus said:
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?
Click to expand...

Yes. I am just changing the year and it would be great just to change it in D5 and have the filename change.
 
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.
 
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).
 
Try this:

Code: 
=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)
 
Tweak155 said:
Try this:

Code: 
=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)
Click to expand...

^This doesn't work.

This works

Code: 
=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
Code: 
"$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
Code: 
 tag?
 
Bird222 said:
^This doesn't work.

This works

Code: 
=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
Code: 
"$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
Code: 
 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...
Click to expand...
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top