关于多sheet表格指定日期查找返回名称

微光的夏

大氵寿 发表于 2013-6-19 15:28
懂了.

再问一句, 比如6月1号,你的人去了3319,3418二家店, 那么在日历表的 6月1号下面,就要自动写入:"3 ...

哥，我仔细看了一下你的公式，貌似我去4个门店，也可以显示出来的。我自己试了一下。

大氵寿

微光的夏 发表于 2013-6-19 15:55
哥，我仔细看了一下你的公式，貌似我去4个门店，也可以显示出来的。我自己试了一下。

那是最笨的方法, 只去4个门店,就每个表都比较一下,, 如果你60多个门店,那就不好这么做了.

微光的夏

大氵寿 发表于 2013-6-19 16:05
那是最笨的方法, 只去4个门店,就每个表都比较一下,, 如果你60多个门店,那就不好这么做了.

额。。。哥，我已经用笨办法录入了30多家了。。。。等我录完了看看。。。

大氵寿

微光的夏 发表于 2013-6-19 16:15
额。。。哥，我已经用笨办法录入了30多家了。。。。等我录完了看看。。。

如果这样,那你用自定义名称来做,非常简单

公式生成在 excel 里做, 做好一家的公式, 然后直接拉一下, 60多家的公式一次到位,然后全部复制到自定义名称里.

以后日历里只需要输入一个自定义名称就可以了, 不用每个单元格都要输入60多家的统计公式..

微光的夏

这样吗？我给你我自己加的公式看看。
=IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3319'!$D:$D,0)),,"东门茂业,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4982'!$D:$D,0)),,"太阳百货,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3418'!$D:$D,0)),,"金光华,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3902'!$D:$D,0)),,"鸿展广场,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3694'!$D:$D,0)),,"万象城,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3810'!$D:$D,0)),,"华强北茂业,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5511'!$D:$D,0)),,"KKMALL,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4076'!$D:$D,0)),,"中信城市广场,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3622'!$D:$D,0)),,"深南茂业,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3634'!$D:$D,0)),,"福田CoCoPark,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3693'!$D:$D,0)),,"怡景中心城,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5512'!$D:$D,0)),,"京基百纳,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4998'!$D:$D,0)),,"福田君尚,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4999'!$D:$D,0)),,"深国投,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4368'!$D:$D,0)),,"益田假日,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4008'!$D:$D,0)),,"南山海雅,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3576'!$D:$D,0)),,"蛇口花园城,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3628'!$D:$D,0)),,"南山崇尚,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3878'!$D:$D,0)),,"南山海岸城,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4231'!$D:$D,0)),,"南山茂业,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5092'!$D:$D,0)),,"南山欢乐颂,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3741'!$D:$D,0)),,"宝安港隆城,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3843'!$D:$D,0)),,"宝安海雅,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4561'!$D:$D,0)),,"西部珠宝城,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4973'!$D:$D,0)),,"沙井万家,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5131'!$D:$D,0)),,"沙井佳华,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5173'!$D:$D,0)),,"创业天虹,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5411'!$D:$D,0)),,"宝安时代城,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5412'!$D:$D,0)),,"西乡天虹,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5341'!$D:$D,0)),,"缤纷海雅,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3957'!$D:$D,0)),,"龙华大润发,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4000'!$D:$D,0)),,"阳光湾畔,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4009'!$D:$D,0)),,"龙华天虹,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4766'!$D:$D,0)),,"龙华佳华,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4727'!$D:$D,0)),,"梅林天虹,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5135'!$D:$D,0)),,"石岩佳华,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5261'!$D:$D,0)),,"民治天虹,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5490'!$D:$D,0)),,"松岗佳华,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4118'!$D:$D,0)),,"龙岗天虹,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3966'!$D:$D,0)),,"龙岗世贸,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5513'!$D:$D,0)),,"龙岗摩尔城,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4593'!$D:$D,0)),,"横岗天虹,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5415'!$D:$D,0)),,"龙岗CoCoPark,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4395'!$D:$D,0)),,"公明天虹,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5147'!$D:$D,0)),,"公明佳华,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3705'!$D:$D,0)),,"惠州海雅,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4432'!$D:$D,0)),,"港惠新天地,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3764'!$D:$D,0)),,"惠州数码街,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5344'!$D:$D,0)),,"惠州天虹,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5115'!$D:$D,0)),,"惠州华贸,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5234'!$D:$D,0)),,"惠州丽日,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4073'!$D:$D,0)),,"惠州吉之岛,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'4355'!$D:$D,0)),,"惠阳永盛,")&IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'5257'!$D:$D,0)),,"惠阳新一城,")

大氵寿

微光的夏 发表于 2013-6-19 17:02
这样吗？我给你我自己加的公式看看。
=IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$70,"月",,1),P72),'3319 ...

你手工做的??? 真有耐心....

做法:
把60家的代码都写在A列,从A1开始，

这是一家的公式:
IF(ISERROR(MATCH(DATE(2013,SUBSTITUTE($J$29,"月",,1),O31),'3319'!$D:$D,0)),,"3319,")
输入到B1, （把他当字符串，前面千万不要加 “=” 符号，否则真变成公式)
只需要把里面的关键字 3319替换一下，这个公式你会吧？ 然后下拉，
那60家的公式就都好了。


然后按 ctrl+f3， 调出自定义名称功能，，把这60家的公式集合到一个名称里，比如定义名称叫 DATA.
那你以后每个单元格只需要调用 DATA 就可以了。。不需要每个单元格都输入这么长的公式。。

大氵寿

你的这个问题，我现在弄不出来， vba可以做到，

或者用上面的笨办法，

微光的夏

大氵寿 发表于 2013-6-19 17:20
你的这个问题，我现在弄不出来， vba可以做到，

或者用上面的笨办法，

已经十分的感谢您了，实在太感谢你了。

微光的夏

大氵寿 发表于 2013-6-19 17:09
你手工做的??? 真有耐心....

做法:

还一个问题，单元格怎么调用DATA，我DATA已经做好了。。但是不知道怎么调用，名称管理器还没有接触过，我自己先弄弄看，不行再麻烦你。