@Dan H - You're making it hard for yourself. No incantations, incense, or chicken feet are needed. And you've never irretrievably screwed anything up, if you keep your wits about you.
There are more than one way to skin this cat. Here are the two easiest:
Select the data you want to plot. If it is in multiple ranges, select one range, then hold Ctrl while selecting the other ranges. Go to Insert tab, select the chart type you want.
Insert XY Scatter chart. Edit Data: select series, click Edit. Clear contents of Series Name box, either type name in box or click in box and select cell containing series name. Clear contents of Series X Values box, click in box and select range containing X values. Clear contents of Series Y Values box, click in box and select range containing Y values. Click OK twice.
Note: it is not "blank" cells that cause the X values to revert to 1,2,3,... It is formulas that return "", which is not blank, since it contains a formula that returns a (short) text value. Replace "" in your formulas with NA(), which returns #N/A in the cell. In XY and Line charts, Excel will skip points with #N/A for X or Y value, omitting the point altogether, connecting points on either side with a line (if lines are used in the series), without messing up the axis.
If you need the cells for further calculations, you need two ranges, one that has #N/A for charting, one with whatever won't mess up the calculations (perhaps "").
Someday Microsoft may finally give us the BLANK() function we've requested for so long.