Help with {=IFERROR(SMALL

jaihawk8

New Member
Joined
Mar 23, 2018
Messages
37
Someone helped me a long time with these files and now something seems to not be working and I can't figure it out for the life of me.

File: Q4 - Rep.xlsx - This contains data for Q4 for our Sales Reps. Column AA contains the Sales Rep name, which we want to use in the next file.
File: Rep - Quarter 4.xlsm - This contains a tab for each Sales Rep. The Sales Rep name is entered into cell A1. Starting with A5, it is supposed to bring in each row from file Q4 - Rep.xlsx and then the information in the header.

If you look at Blake Brunk as an example, it is pulling in everything through the City of Nampa, but it is not pulling in Gem County (row 1283) or Mission Aviation Fellowship (row 1309). I'm not sure why it's not doing that.

Also, what is the number in column A? I thought it was the row line from the file, but that doesn't seem to match up with anything, as Canyon County Elections is on row 777 in the file, not row 38.


Q4 - Rep.xlsx

Rep - Quarter 4.xlsm

Thank you in advance for the help. If I can get Blake Brunk's working, I believe I should be able to copy it over to the other tabs to make them work.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,476
Office Version
  1. 365
Platform
  1. Windows
I can't access the files to see what is wrong with them due to security limitations.

Things to check would be that the formulas cover the full range of data, you mention rows 1283 and 1309 so a formula that only looks as far as row 1200 is going to miss both of them.
Another thing to check would be less obvious typos, if the name "Blake Brunk" is being used as criteria, then something as simple as an additional space after Brunk in one of the rows would mean that it is not a match.
 

jaihawk8

New Member
Joined
Mar 23, 2018
Messages
37
I can't access the files to see what is wrong with them due to security limitations.

Things to check would be that the formulas cover the full range of data, you mention rows 1283 and 1309 so a formula that only looks as far as row 1200 is going to miss both of them.
Another thing to check would be less obvious typos, if the name "Blake Brunk" is being used as criteria, then something as simple as an additional space after Brunk in one of the rows would mean that it is not a match.

I posted the links to Google Drive and then shared the links. Is there something else that I can do to share the links? Dropbox maybe?

I did check the formulas and originally they were only going to row 1000 and I changed that to 3000 (information stops at 1500), but it still didn't work. I also checked to make sure that "Blake Brunk" was right and that there wasn't an extra space or anything.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,476
Office Version
  1. 365
Platform
  1. Windows
It looks like the formulas might be working with stale data and that the sheet needs refreshing, to do that go to the Data tab in excel, click on Edit Links, then Update Values.

I can't test that as it requires login credentials for your sharepoint server, but when I break the link and point it to the copy that you attached to your earlier post, the formulas return the correct results.
 
Solution

jaihawk8

New Member
Joined
Mar 23, 2018
Messages
37
Thanks all. I redid the links to everything and got it working. I had allowed it to update when it opened, so I'm not sure why it wasn't actually updating, but redoing the links to the other sheet worked.
 

Forum statistics

Threads
1,147,564
Messages
5,741,856
Members
423,691
Latest member
Fahad987

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top