So there's something I've discovered since struggling with the ever-temperamental VLOOKUP, and it's the much more awesome Index Match. There has not been a single thing that I've wanted to instead use a VLOOKUP for since stumbling upon the magic of Index/Match. I mean, they might be common knowledge, but in my travels it was always vlookup, vlookup, vlookup. So question to those more knowledgeable than me, what am I missing out on when pretty much abandoning the vlookup?
I love the simplicty - =index(sheet2!A:A,match(A2,sheet2!B:B,0)).- you're simply asking, please give me a value in the column that I'm looking for, and the way you find it is take the value in cell A2, find it in the second sheet's column B, and return the value in that sheet's column A.
And at work, a multiple-criteria index/match just allowed me in 10 minutes to do something that was taking two people days to do (though I won't act surprised when I discover all these examples of inefficiency across our economy).
So, this somewhat segues into the next project I've started at work: quick background, I work at a reasonably large hospital in the Boston area, and we are doing a lot of testing which requires test patients - the software vendor doesn't have a tool to create them, nor does our PM firm - so I ended up figuring out how to put together a workbook that will allow someone to simply type in the naming convention of the test patients, and the number that are needed, and it populates every field that will satisfy a complete patient record in the system (save for two items that aren't generated until post-registration). One will then copy and paste the values into the import file, generate the txt file required, and load it into the system. No trudging through all of the registration screens is required.
That's all well and good, but I'm adamant of having a process flow being as absolutely efficient as possible, i.e., remove all of the steps that someone could fuck up along the line. So the solution I've come up with in regards to this workbook is taking the copy/paste values bit out of the equations, and having the values auto-populate into the import spreadsheet. The sheet is macro-enabled, and will only export values within the columns and rows bounded by cells filled with black. Getting the values to populate into the import spreadsheet is the easy part, but I'm looking for a formula that will say:
Sheet1 | Import
A2 X (value Sheet1)
B2 X (value Sheet1)
C2 if C2 is blank, then fill cell with black
naturally, it would be something like =IF(Sheet1!C2="",(fill with black),Sheet1!C2)
TL;DR version:
How do I get the cell to fill with black?
I'm searching up and down on Google, and the major issue seems to be that Microsoft decided to name two different things in excel "fill".
And I'm now realizing the answer if probably conditional formatting, but I have to test if that works when a formula says to ="".
