Formula to Calc how many Versions of BIOS each PC is back from Most Current

kymikee

New Member
Joined
Aug 2, 2013
Messages
4
I have a Spreadsheet where on One of the Tabs, I've listed each different Computer that I have at the Company that I work at (26 Different Dell Computers like 3040, 3050, 3060, etc). I've gone into Dell's web site and fouind out the latest BIOS Version of each Computer. On another Tab, I've pulled in all of the PC's, Laptops and Tablets I've got in my Location from AD (Put it into a Tab Named BIOSCheck). I've got each Type of Computer (3040, 3050, 3060, etc) listed in this spreadsheet also and thru Powershell, I've gotten what BIOS Version is on each PC, Laptop and Tablet and entered that into my BIOSCheck Tab. I've already created a Formula to pull the column in the BIOS Tab that contains the latest BIOS Version. Now I'd like to create a Formula that starts with the Column that has the latest BIOS Version and then counts back (Not counting the first Cell as it contains the Latest BIOS Version) until it finds the BIOS Version that each Computer has and then tells me how many Columns it had to count back from the Column of the up-to-date BIOS Version (ex. Using Powershell, I pulled one Laptops info that was a 7480 and found the BIOS Version to be 1.9.3. The Most Current Version is 1.21.1. BIOS Version 1.9.3 is 13 back from the Most Current Version. I'd like the formula to automatically tell me that this PC's BIOS is 13 versions back. I'd like to then copy each formula to all of the other Columns and have them tell me how many BIOS Versions back their BIOS is). How would I do this??

So Far, I've got: =xlookup(j11,BIOS!G6:G27,xlookup(K11,BIOS!L6:AC27,"")) **** This gives me #VALUE! as a result

J11 = BIOSCheck Tab; contains Dell Computer Type
BIOS!G6:G27 - BIOS Tab; contains Dell Computer Type
K11 = BIOSCheck Tab; contains Current BIOS Version
BIOS!L6:AC27 = BIOS Tab; Contains All of the BIOS Versions of each Computer as gotten from Dell's Web Site for each Type of Dell Computer

Any Help would be Greatly Appreciated!!
Thanks
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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