Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Volatile formula's, speeding up calculations and match first and last name in separate columns  to return value from a third.

Posted on 2016-10-28
6
Medium Priority
?
117 Views
Last Modified: 2016-10-28
Hi,
First time posting as I'm struggling with a formula that I thought was pretty good and took me awhile to write, as I'm not exactly an expert with Excel. Anyway I'm not ashamed to say I need the help.

The gist of it is simple, the formula is not. Basically I have a list of locker numbers and employees, and other info but I can alter formulas to suit. What I need to do is rather than concatenate the names I want to, in a separate tab, match the surname in column A and the first name in column B to the list of names with locker numbers,surname in A, first name in B, and return column C with the locker number in to the formula cell, whilst leaving this cell blank for missing info in cells containing no names or locker numbers.

They may have several garments and I need to know the locker number on the sheet for each one so the formula is to be dragged down to find the locker for each employee for all their garments, which will be the same locker number for that employee.

The formula I have wrote I believe is classed as a volatile formula and as such is taking far too long to calculate when I amend one of the connected cells. I know you can turn off the calculate options to populate fields but the people who will be using the spreadsheet won't be as clued up on Excel.

Here is the formula currently in place:

=IF(OR(A2=0,B2=0)," ",IF(LOOKUP(2,1/(('Individual PPE'!A:A='Full Wearer and ID Code List'!A2)*('Individual PPE'!B:B='Full Wearer and ID Code List'!B2)),'Individual PPE'!E:E)=0," ",LOOKUP(2,1/(('Individual PPE'!A:A='Full Wearer and ID Code List'!A2)*('Individual PPE'!B:B='Full Wearer and ID Code List'!B2)),'Individual PPE'!E:E)))

It works as intended apart from slowing the sheet down to a halt when amendmending info.

Can someone please advise a way around this or a rejigged formula that will not be volatile and calculate quicker?

Many thanks in advance.

Neil
0
Comment
Question by:Neil Hughes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 41863681
HI,

Do you really need to calculate it on the whole columns?

Regards
0
 

Author Comment

by:Neil Hughes
ID: 41863693
Hi,
Thanks for your reply.

I understand it's easier to hide a column and concatenate the info but this is a sheet I'm putting together to monitor workwear on each site individually within our group. By hiding cells and trying to account for so many different variations across the group i.e. 50 employee's on one site 200 on another, I thought it best to keep all columns visible and allow people to drag down the formula's for additional employees etc as and when they add them or take them off.

By hiding columns and cells required for working out certaain formulas, should something go amiss there's not a visible solution. This is why I wanted a formula in a cell that matches the sets of columns rather than a hidden one with the names concatenated.

I hope this makes sense.

Thank you,
0
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 41863722
why don't you use 'Individual PPE'!A1:A10000 instead of 'Individual PPE'!A:A
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Neil Hughes
ID: 41863747
Will that make a difference?
0
 
LVL 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 2000 total points
ID: 41863756
it should since it will reduce significantly the number of calculations Excel has to do
'Individual PPE'!$A$1:$A$10000
0
 

Author Comment

by:Neil Hughes
ID: 41863769
Hey,
Thank you very much for this.

It has effectively reset the speed of calculations to what it is normally, more or less instant.

Appreciate the help. Makes me chuffed to bits that the time I put into that formula isn't wasted too and that it does work, and just needed the final touches adding to it.

Thanks again.

Neil
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial is posted by Aaron Wojnowski, administrator at SDKExpert.net.  To view more iPhone tutorials, visit www.sdkexpert.net. This is a very simple tutorial on finding the user's current location easily. In this tutorial, you will learn ho…
Windows programmers of the C/C++ variety, how many of you realise that since Window 9x Microsoft has been lying to you about what constitutes Unicode (http://en.wikipedia.org/wiki/Unicode)? They will have you believe that Unicode requires you to use…
Video by: Grant
The goal of this video is to provide viewers with basic examples to understand and use nested-loops in the C programming language.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
Suggested Courses

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question