Help with {=IFERROR(SMALL

jaihawk8

New Member
Joined
Mar 23, 2018
Messages
46
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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
Back
Top