![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
Time saving functions
Mar. 29th, 2007 05:45 pmSadly, nothing as cool as a TARDIS.
But, I did learn about a function in MS Excel today that would have saved me much time had I known about it a few months ago. When you get up from falling over because I said something positive about Microsoft, especially MS Office, you can read about it.
You can use VLOOKUP or HLOOKUP to reference data based on the contents of matching cells (somewhat like a SQL query). So if you have a sorted list on one tab, and an unsorted set of the same things on another, rather than painstakingly copy and pasting from tab to tab, you use these functions to copy in the right data.
It is important since I get a dataset every week where the objects (SAN switches in this case) are in a different order. Now I can say look at the rows in column B (switch name), when you find the same name in a row in column X on Sheet2, put the value from column EE for that row in this cell. We used absolute references ($B$2:$EE$99) so we could make use of the Copy down function to populate the other cells of this column. Yes, there are really over 135 columns in the source data. Not all of them are used and the designer of the spreadsheet really liked merged columns. Also, they either got paid by line of code, or by how convoluted the code looked.
But, I did learn about a function in MS Excel today that would have saved me much time had I known about it a few months ago. When you get up from falling over because I said something positive about Microsoft, especially MS Office, you can read about it.
You can use VLOOKUP or HLOOKUP to reference data based on the contents of matching cells (somewhat like a SQL query). So if you have a sorted list on one tab, and an unsorted set of the same things on another, rather than painstakingly copy and pasting from tab to tab, you use these functions to copy in the right data.
It is important since I get a dataset every week where the objects (SAN switches in this case) are in a different order. Now I can say look at the rows in column B (switch name), when you find the same name in a row in column X on Sheet2, put the value from column EE for that row in this cell. We used absolute references ($B$2:$EE$99) so we could make use of the Copy down function to populate the other cells of this column. Yes, there are really over 135 columns in the source data. Not all of them are used and the designer of the spreadsheet really liked merged columns. Also, they either got paid by line of code, or by how convoluted the code looked.
