plot scatter line based on lookup value

M

mrdixon

New Member
Joined
Feb 12, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello all. First post.

I have two design cases, LE and HE and their results as a function of depth. I have these at 95 locations.

I would to plot them, with a lookup function for any one of the locations. So I enter the location into a cell, it finds the depth and LE / HE results for each location.

It worked using a Index () and Match () combo....only it then didnt for any other than the first location.

Also, the results stop at different depths, but the scatter line goes haywire when some of the results become zero...

Help greatly appreciated!

Book2
ABCDEFGHIJKLMNOPQ
1Reviewed By
2LocationC02Reviewed Date
3DepthLEHECopied By
4C02 | DepthC02 | LEC02 | HEDated Copied12/02/202112/02/202112/02/202112/02/2021
52.4309.69404612.5511234
62.6307.91524608.184Note. If text in red. Problem with results.A02B02B03B04
72.8306.12644603.3A02 | DepthA02 | LEA02 | HEB02 | DepthB02 | LEB02 | HEB03 | DepthB03 | LEB03 | HEB04 | DepthB04 | LEB04 | HE
83304.32964598.001123456789101112
93.2302.52284602.744DepthLEHEDepthLEHEDepthLEHEDepthLEHE
103.4300.70804612.02708E-050.0001709.2E-050.0001903.5E-057E-0509.2E-050.00019
113.6298.88324618.7120.221.681845.33850.224.779351.00580.29.5221618.86850.224.779337.184
123.8297.14674616.9210.463.5165132.8180.472.5903149.420.427.89555.27480.451.984571.753
134295.30494630.5570.6139.907292.5570.6144.486281.2650.661.444121.7530.689.833121.915
144.2293.45514648.3920.8261.247493.3720.8206.451402.3490.8114.734205.6630.8148.555197.126
154.4291.69264671.0061322.373595.511247.087470.0121158.219236.0951184.881241.373
164.6289.92114664.4581.2320.562643.4191.2257.765488.2141.2157.377233.7971.2191.473247.513
174.8288.14064690.181.4318.742639.0631.4271.066511.9041.4156.583231.1881.4200.664256.37
185286.35214727.3251.6317.009631.0281.6288.318542.9561.6155.724228.9311.6208.738263.813
195.2284.55564767.4311.8315.17627.0921.8311.383584.2891.8154.913226.5831.8221.898276.76
205.4282.75014766.6472313.322622.7372343.755643.5472154.096224.0722236.608291.415
215.6280.93664837.1822.2311.561617.8692.2392.42711.6532.2153.269221.7652.2255.751310.225
225.8279.11414943.3292.4309.694612.5512.4431.586707.4832.4152.434219.4422.4280.537335.258
236277.37994934.6292.6307.915608.1842.6464.53712.712.6151.648216.8842.6318.567377.04
246.2275.541441079.662.8306.126603.32.8465.463857.9382.8150.798214.6032.8374.154437.69
256.4273.791241087.873304.33598.0013614.777982.8193149.94212.3073452.469532.94
266.6271.936741152.753.2302.523602.7443.2611.1831050.443.2149.131209.8493.2449.557632.486
276.8270.169541271.953.4300.708612.0273.4612.3991114.083.4148.257207.5223.4446.771627.911
287268.395341268.833.6298.883618.7123.6678.0171323.323.6147.432205.2543.6443.841621.971
297.2266.612141365.833.8297.147616.9213.8701.3171360.123.8146.601202.753.8441.038617.086
307.4264.820941416.174295.305630.5574718.7831402.584145.76200.5254438.224611.216
317.6263.119041711.874.2293.455648.3924.2742.4331439.734.2144.913198.2864.2435.402606.126
327.8261.405141933.654.4291.693671.0064.4766.5151476.884.4144.058195.8134.4432.572600.644
338259.590941972.574.6289.921664.4584.6787.2691514.034.6143.196193.5444.6429.732597.582
348.2257.865042023.244.8288.141690.184.8808.0231558.264.8142.383191.3364.8426.883606.405
358.4256.034842062.525286.352727.3255832.8891595.775141.506188.8945424.161598.965
368.6254.293942101.795.2284.556767.4315.2853.8381633.275.2140.678186.6575.2421.295611.083
378.8252.546042063.665.4282.75766.6475.4874.7881673.595.4139.787184.4065.4256.446617.384
389250.789142110.865.6280.937837.1825.6900.4371716.785.6138.945181.9965.6264.835616.938
399.2249.025242233.145.8279.114943.3295.8921.5821754.645.8138.096179.7935.8271.666633.649
409.4247.254342272.776277.38934.6296942.7281801.696140.876182.7886278.497633.826
419.6245.475442326.636.2275.5411079.666.2935.4411839.916.2142.032184.1556.2287.138646.212
429.8243.785842366.616.4273.7911087.876.4964.1951878.126.4144.813187.146.4294.031669.953
4310241.992942369.396.6271.9371152.756.61011.841916.336.6147.596190.1216.6300.925663.682
4410.2240.288342461.876.8270.171271.956.81033.181917.226.8148.743191.4596.8309.816698.127
4510.4238.480442502.217268.3951268.8371060.592003.737151.527194.437316.772714.21
4610.6236.761842542.547.2266.6121365.837.21082.132013.277.2152.664195.7487.2323.729719.64
4710.8235.036242582.887.4264.8211416.177.41103.672084.887.4155.45198.7097.4332.87723.995
4811236.419772639.927.6263.1191711.877.61115.12131.467.6156.579200.0077.6339.889788.162
4911.2237.491182680.617.8261.4051933.657.81153.62170.387.8159.365202.9597.8346.908777.382
5011.4238.860712721.38259.5911972.5781175.332161.358160.485204.2398356.301820.656
5111.6240.223242779.768.2257.8652023.248.21204.312261.328.2163.272207.1818.2363.382846.132
5211.8241.274642820.818.4256.0352062.528.41223.42296.138.4166.064210.1228.4372.963844.658
5312242.623172861.858.6254.2942101.798.61398.972331.058.6167.175211.3778.6374.305869.32
5412.2243.96472901.098.8252.5462063.668.81426.132379.998.8169.968214.318.8371.696885.876
5512.4244.996112963.129250.7892110.8691883.132415.229171.07215.5489368.944899.283
5612.6246.324643004.529.2249.0252233.149.21308.252450.449.2173.865218.4739.2366.32950.881
5712.8247.342043045.929.4247.2542272.779.41327.612485.679.4174.744219.4179.4363.645940.03
5813248.656573107.569.6245.4752326.639.61355.412536.199.6177.539222.3349.6361.0051009.94
5913.2249.660983149.319.8243.7862366.619.81377.732571.49.8178.626223.5399.8358.3581090.93
6013.4250.963513191.0710241.9932369.39101400.052616.1510181.422226.4510355.5681094.21
6113.6252.259043254.1310.2240.2882461.8710.21431.412672.4910.2182.503227.63910.2352.9051156.91
6213.8253.244443296.2310.4238.482502.2110.41453.922712.8210.4185.079230.2610.4350.3711220.25
6314254.526973338.3410.6236.7622542.5410.61476.442753.1610.6187.879233.16310.6347.6931207.99
6414.2255.499383380.4410.8235.0362582.8810.81508.572793.510.8188.951234.33310.8345.0091447.72
6514.4256.769913445.2711236.422639.9211927.5271676.1611191.755237.2311342.3171856.88
6614.6257.730313487.7311.2237.4912680.6111.2942.1921701.7111.2192.592238.09711.2339.7531899.55
6714.8258.683723530.1911.4238.8612721.311.4963.3891738.7711.4195.395240.98811.4336.9542230.85
6815259.935253596.4311.6240.2232779.7611.6978.1791764.5411.6196.455242.13111.6334.3763110.29
6915.2260.877653639.2511.8241.2752820.8111.8992.9691790.3111.8199.028244.72511.8331.793179.57
7015.4414.3583414304.112242.6232861.85121014.671828.2512200.08245.85512331.3093164.48
7115.6440.407342344.5712.2243.9652901.0912.21029.581854.2412.2202.888248.73812.2332.9323261.66
7215.8477.238342387.3512.4244.9962963.1212.41712.643176.9312.4205.462251.32312.4334.1223324.21
7316532.942342414.6612.6246.3253004.5212.61747.123218.3212.6206.507252.43812.6335.7313365.61
7416.2626.412342441.9812.8247.3423045.9212.81770.423259.7212.8209.08255.01612.8336.9083407.01
7516.4814.315342469.2913248.6573107.56131793.723322.4313210.119256.11913338.5033470.97
7616.61379.98732513.1713.2249.6613149.3113.21817.023364.1813.2212.691258.69113.2339.6663512.72
7716.81425.74432540.7113.4250.9643191.0713.41852.483369.2213.4213.725259.78413.4341.24813885.4
78171441.66222568.2413.6252.2593254.1313.61875.973470.0513.6216.542262.65713.6342.8231033.74
7917.21457.5801261313.8253.2443296.2313.81899.473512.1613.8217.324263.43313.8343.9651046.66
8017.41483.36842640.7514254.5273338.34141912.483554.2614220.142266.30314345.5271066.88
8117.61499.41162668.5114.2255.4993380.4414.21959.413596.3714.2220.917267.06714.2346.6571079.92
8217.81517.46032717.2614.4256.773445.2714.41983.13662.2614.4223.737269.93614.4348.2041092.95
83181545.78662748.3414.6257.733487.7314.62020.133704.7214.6226.309272.49514.6349.3221113.59
8418.21563.97632779.4214.8258.6843530.1914.82044.013747.1814.8227.075273.24314.8350.4321126.73
8518.41582.1662829.1615259.9353596.43152067.93814.4815260.699309.58415351.961147.69
8618.61611.05632860.4815.2260.8783639.2515.22105.713857.315.2267.275316.31215.2353.0571160.94
8718.81629.3872891.8115.4414.35814304.115.42129.793900.1115.4276.005325.2515.4354.5741174.18
88191647.71772942.5215.6440.4072344.5715.62153.883942.9215.6282.606331.99115.6355.6591195.57
8919.21675.11962970.9315.8477.2382387.3515.82192.47401215.8289.559339.13915.8357.1641208.92
9019.41691.53892999.3516532.9422414.66162216.754055.1616298.013347.72116414.2591222.27
9119.61707.95823027.7616.2626.4122441.9816.22241.034098.3316.2304.648354.47816.2424.4781244.08
9219.81735.86153076.4416.4814.3152469.2916.42265.314168.8216.4311.658361.66616.4437.41257.54
93201752.40613105.0816.61379.992513.1716.62304.884212.3416.6320.154370.27516.6453.8331271
9420.21768.95073133.7116.81425.742540.7116.82329.364255.8616.8326.822377.04916.8475.2881293.23
9520.41797.35543183.27171441.662568.24172353.834299.3817333.889384.27917504.2831306.79
9620.61814.02533212.1217.21457.58261317.22394.194371.6417.2342.43392.91617.2545.3521320.36
9700017.41483.372640.7517.42418.864415.5217.4349.131399.70817.4607.5591343.01
9800017.61499.412668.5117.62443.534459.3917.6356.258406.98117.6712.342108.91
9900017.81517.462717.2617.82487.784537.9817.8364.842415.64717.8922.6812129.76
100000181545.792748.34182515.754587.12181640.782894.43181242.862150.61
10100018.21563.982779.4218.22543.724636.2618.21656.952922.418.21255.12186.08
10200018.41582.172829.16018.41673.112970.1318.41267.352207.1
RESULTS
Cell Formulas
RangeFormula
A4:C4A4=$B$2&" | "&A3
A5:A102A5=INDEX($F10:$JO$506, MATCH($B$2,$F$6:$KG$6,0),MATCH($A$3,$F$9:$KG$9,0))
B5:B102B5=INDEX($F10:$JO$506, MATCH($B$2,$F$6:$KG$6,0),MATCH($B$3,$F$9:$KG$9,0))
C5:C102C5=INDEX($F10:$JO$506, MATCH($B$2,$F$6:$KG$6,0),MATCH($C$3,$F$9:$KG$9,0))
F7,I7,L7,O7F7=F6&" | "&F9
G7,J7,M7,P7G7=F6&" | "&G9
H7,K7,N7,Q7H7=F6&" | "&H9
 

Attachments

  • 2021-02-12 excel-issue.jpg
    2021-02-12 excel-issue.jpg
    178 KB · Views: 10

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Welcome to the board! Give this a try to see if it solves your issue. Also, note that I unmerged your location label cells in row 6. Merged cells can cause some issues, so I typically leave the label in the first relevant cell and then "center across selection" to create the appearance of merged cells.
MrExcel20210209.xlsx
ABCDEFGHIJKLMNOPQ
1Reviewed By
2LocationA02Reviewed Date
3DepthLEHECopied By
4A02 | DepthA02 | LEA02 | HEDated Copied44239442394423944239
508.015E-050.00016761234
60.221.68184945.338465Note. If text in red. Problem with results.A02B02B03B04
70.463.516495132.81802A02 | DepthA02 | LEA02 | HEB02 | DepthB02 | LEB02 | HEB03 | DepthB03 | LEB03 | HEB04 | DepthB04 | LEB04 | HE
80.6139.90728292.5572123456789101112
90.8261.24717493.372DepthLEHEDepthLEHEDepthLEHEDepthLEHE
101322.37314595.5103408.015E-050.000167600.00009160.000188600.00003526.975E-0500.00009160.0001886
111.2320.56204643.419340.221.68184945.3384650.224.77925651.0057730.29.522159718.8684840.224.77925637.184
121.4318.74194639.063340.463.516495132.818020.472.590279149.420280.427.89495555.2748030.451.9845371.753
131.6317.00914631.028340.6139.90728292.55720.6144.486281.2650.661.443994121.753370.689.833121.915
141.8315.17004627.092340.8261.24717493.3720.8206.451402.3490.8114.73363205.6630.8148.555197.126
152313.32194622.737341322.37314595.510341247.08734470.011631158.21915236.094631184.88134241.37263
162.2311.56114617.869341.2320.56204643.419341.2257.76534488.213631.2157.37675233.796631.2191.47334247.51263
172.4309.69404612.551341.4318.74194639.063341.4271.06634511.903631.4156.5828231.187631.4200.66434256.36963
182.6307.91524608.184341.6317.00914631.028341.6288.31834542.955631.6155.7244228.930631.6208.73834263.81263
192.8306.12644603.300341.8315.17004627.092341.8311.38334584.288631.8154.91346226.582631.8221.89834276.75963
203304.32964598.001342313.32194622.737342343.75534643.546632154.09552224.071632236.60834291.41463
213.2302.52284602.744342.2311.56114617.869342.2392.42034711.652632.2153.26857221.764632.2255.75134310.22463
223.4300.70804612.027342.4309.69404612.551342.4431.58634707.482632.4152.43363219.441632.4280.53663335.25792
233.6298.88324618.712342.6307.91524608.184342.6464.53034712.709632.6151.64814216.883632.6318.56663377.03992
243.8297.14674616.921342.8306.12644603.300342.8465.46334857.937632.8150.7982214.602632.8374.15363437.68992
254295.30494630.557343304.32964598.001343614.77667982.818633149.94025212.306633452.46923532.93992
264.2293.45514648.392343.2302.52284602.744343.2611.183341050.44363.2149.13076209.848633.2449.55678632.48592
274.4291.69264671.006343.4300.70804612.027343.4612.399341114.08063.4148.25682207.521633.4446.77087627.91092
284.6289.92114664.458343.6298.88324618.712343.6678.01741323.32163.6147.43233205.253633.6443.84143621.97092
294.8288.14064690.180343.8297.14674616.921343.8701.316621360.11963.8146.60084202.749633.8441.03752617.08592
305286.35214727.325344295.30494630.557344718.783341402.57864145.76036200.524634438.22361611.21592
315.2284.55564767.431344.2293.45514648.392344.2742.432891439.73064.2144.91287198.285634.2435.4017606.12592
325.4282.75014766.647344.4291.69264671.006344.4766.515271476.88264.4144.05838195.812634.4432.57179600.64392
335.6280.93664837.182344.6289.92114664.458344.6787.26921514.03464.6143.19589193.543634.6429.73188597.58192
345.8279.11414943.329344.8288.14064690.180344.8808.023121558.26264.8142.38286191.335634.8426.88297606.40492
356277.37994934.629345286.35214727.325345832.888671595.76765141.50637188.893635424.16059598.96492
366.2275.541441079.66435.2284.55564767.431345.2853.838391633.27365.2140.67834186.656635.2421.29468611.08292
376.4273.791241087.87435.4282.75014766.647345.4874.788111673.59365.4139.78685184.405635.4256.44567617.38363
386.6271.936741152.74735.6280.93664837.182345.6900.436831716.77665.6138.94482181.995635.6264.83547616.93763
396.8270.169541271.94935.8279.11414943.329345.8921.582331754.63665.8138.09579179.792635.8271.66638633.64863
407268.395341268.82836277.37994934.629346942.727841801.69466140.87586182.787636278.4973633.82563
417.2266.612141365.83336.2275.541441079.66436.2935.441341839.90866.2142.03242184.154636.2287.13777646.21163
427.4264.820941416.16836.4273.791241087.87436.4964.195341878.12166.4144.81309187.139636.4294.03136669.95263
437.6263.119041711.87336.6271.936741152.74736.61011.84231916.33466.6147.59649190.120636.6300.92494663.68163
447.8261.405141933.64566.8270.169541271.94936.81033.18361917.22466.8148.74278191.458636.8309.81609698.12663
458259.590941972.56667268.395341268.828371060.59452003.72967151.52678194.429637316.77234714.20963
468.2257.865042023.24267.2266.612141365.83337.21082.13162013.26967.2152.66394195.747637.2323.72859719.63963
478.4256.034842062.51667.4264.820941416.16837.41103.66872084.88367.4155.44954198.708637.4332.87042723.99463
488.6254.293942101.79167.6263.119041711.87337.61115.09832131.46167.6156.57856200.006637.6339.88934788.16163
498.8252.546042063.66467.8261.405141933.64567.81153.59552170.38267.8159.36476202.958637.8346.90826777.38163
509250.789142110.86068259.590941972.566681175.32842161.34668160.48465204.238638356.30077820.65563
519.2249.025242233.13968.2257.865042023.24268.21204.30562261.31568.2163.27244207.180638.2363.38236846.13163
529.4247.254342272.76868.4256.034842062.51668.41223.40092296.13438.4166.06397210.121638.4372.96287844.65763
539.6245.475442326.62868.6254.293942101.79168.61398.9712331.04538.6167.17459211.376638.6374.30523869.31963
549.8243.785842366.61068.8252.546042063.66468.81426.1262379.99138.8169.96772214.309638.8371.69592885.87563
5510241.992942369.39269250.789142110.860691883.1292415.21639171.0702215.547639368.94408899.28263
5610.2240.288342461.86869.2249.025242233.13969.21308.25222450.44139.2173.86493218.472639.2366.31978950.88063
5710.4238.480442502.20569.4247.254342272.76869.41327.61112485.66739.4174.74374219.416639.4363.64523940.02992
5810.6236.761842542.54169.6245.475442326.62869.61355.41352536.18539.6177.53863222.333639.6361.004921009.9449
5910.8235.036242582.87769.8243.785842366.61069.81377.73382571.39539.8178.62585223.538639.8358.357621090.9319
6011236.419772639.922610241.992942369.3926101400.0542616.150310181.42234226.4496310355.567781094.2059
6111.2237.491182680.612610.2240.288342461.868610.21431.40522672.487310.2182.50342227.6386310.2352.905471156.9129
6211.4238.860712721.302610.4238.480442502.205610.41453.92122712.823310.4185.07867230.2596310.4350.37071220.2479
6311.6240.223242779.762610.6236.761842542.541610.61476.43732753.160310.6187.87905233.1626310.6347.69341207.9919
6411.8241.274642820.806610.8235.036242582.877610.81508.57162793.496310.8188.95087234.3326310.8345.009091447.7219
6512242.623172861.850611236.419772639.922611927.526981676.163311191.75485237.2296311342.316791856.8799
6612.2243.96472901.091611.2237.491182680.612611.2942.191511701.714311.2192.59183238.09663
6712.4244.996112963.123611.4238.860712721.302611.4963.389271738.768311.4195.39498240.98763
6812.6246.324643004.520611.6240.223242779.762611.6978.179141764.539311.6196.45453242.13063
6912.8247.342043045.918611.8241.274642820.806611.8992.969011790.310311.8199.0277244.72463
7013248.656573107.562612242.623172861.8506121014.66811828.245312200.08012245.85463
7113.2249.660983149.314612.2243.96472901.091612.21029.58331854.2363
7213.4250.963513191.065612.4244.996112963.123612.41712.63553176.9263
7313.6252.259043254.125612.6246.324643004.520612.61747.11933218.3233
7413.8253.244443296.230612.8247.342043045.918612.81770.41863259.7213
7514254.526973338.336613248.656573107.5626131793.71783322.4273
76   13.2249.660983149.3146
77   13.4250.963513191.0656
78   13.6252.259043254.1256
79   13.8253.244443296.2306
80   14254.526973338.3366
RESULTS
Cell Formulas
RangeFormula
A4:C4A4=$B$2&" | "&A3
A5:A80A5=IF(ROWS(A$5:A5)<=COUNT(INDEX($F$10:$JO$506,,MATCH($B$2,$F$6:$KG$6,0))),INDEX($F$10:$JO$506,ROWS(A$5:A5),MATCH($B$2,$F$6:$KG$6,0)),"")
B5:B80B5=IF(ISNUMBER($A5),INDEX($F$10:$JO$506,ROWS(A$5:A5),MATCH($B$2,$F$6:$KG$6,0)+1),"")
C5:C80C5=IF(ISNUMBER($A5),INDEX($F$10:$JO$506,ROWS(A$5:A5),MATCH($B$2,$F$6:$KG$6,0)+2),"")
F7,O7,L7,I7F7=F6&" | "&F9
G7,P7,M7,J7G7=F6&" | "&G9
H7,Q7,N7,K7H7=F6&" | "&H9


Also, the general form for the plots is
=SERIES(RESULTS!$C$4,RESULTS!$A$5:$A$100,RESULTS!$C$5:$C$100,2)
It should be okay for the plotted range to exceed the number of data points.
 
Last edited:
Upvote 0
After looking at this one more time, I think my last comment is not correct...about the plot range. To ensure better behavior when plotting, I modified the plots to use dynamic ranges. This is implemented by establishing three named ranges:
ChartDepth =OFFSET(RESULTS!$A$5,,,COUNT(RESULTS!$A$5:$A$100))
ChartLE =OFFSET(RESULTS!$B$5,,,COUNT(RESULTS!$A$5:$A$100))
ChartHE =OFFSET(RESULTS!$C$5,,,COUNT(RESULTS!$A$5:$A$100))

Then when inserting plots into the XY scatter chart, you would specify...

for the LE curve:
Series X values as RESULTS!ChartDepth
Series Y values as RESULTS!ChartLE

and for the HE curve:
Series X values as RESULTS!ChartDepth
Series Y values as RESULTS!ChartHE

These references for the X and Y values point back to the named ranges, which will automatically adjust the number of points plotted, as determined by the OFFSET and COUNT functions. I also added a scroll bar to more easily navigate through the 95 data sets, so that you don't need to type the data set name. If an error occurs, the scroll bar may give erratic behavior and Excel will display pop up error messages and the scatter plot may not update. The easiest way to recover from this is to manually enter a data set number (e.g. 1 for the first set) into cell C2...and then the scroll bar arrow and drag bar should work again. Since XL2BB does not capture these things, a working example is available here:

Dropbox

www.dropbox.com www.dropbox.com
 
Upvote 0
Solution
I swapped the X and Y, so that depth was on the vertical axis.
Also, I noted that there was a residual issue where if the number of rows for each location changed, the plots plotted incorrectly! I added the IF() function to show "#N/A" where there are no LE or HE values for increasing depths and now it works perfectly. All thanks to your wisdom, thanks heaps KRice!
 
Upvote 0
I'm glad it's working. I noticed the same thing--that the plots did not appear correct when attempting to fix the number of data points; when in reality, the number of rows to be plotted changed between data sets. The dynamic named ranges described in post #3 completely resolved that issue for me, as each plot will automatically be customized to plot only the number of data points present.
 
Upvote 0
I reopened my version of the file and was surprised to see the same problem that you described. For some reason, the dynamic named ranges seem to have been missing and the plots were not correct. I wonder if I failed to save the revised file before posting it. In any case, the improved version is available at the link below (same name), and I included the plot sets both ways...Depth vs. Value and Value vs. Depth, depending on preferences. One finer point: I noted that when attempting to edit the Data Series by right clicking on a curve and selecting "Select Data...", I had to first delete the Series Name before I could successfully edit the Series X Values and Series Y Values with the named ranges mentioned in post #3. Then after adding the named ranges in those two fields, the cell reference that holds the Series Name can be entered again. Otherwise, an error message popped up every time I attempted to edit the fields holding the series descriptions.

Dropbox

www.dropbox.com www.dropbox.com
 
Upvote 0
You must log in or register to reply here.

Similar threads

Sean15
  • Solved
Rewrite formula so 2nd, 3rd…occurrence of lookup value return blank cell
Replies
6
Views
255
Peter_SSs
Peter_SSs
W
  • Solved
Index and Match on Multiple Columns with Duplicate Values
Replies
4
Views
726
wsr429
W
C
  • Solved
index match excel formula to vba code
Replies
9
Views
365
DanteAmor
DanteAmor
C
  • Question Question
Alternative to Index Match to sum up
Replies
4
Views
220
Fluff
Fluff
J
  • Question Question
Index, Match, Large based on two Criteria
Replies
3
Views
114
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

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