这个Excel公式有什么问题？ [英] What is wrong with this Excel Formula?

问题描述

我有这个公式 = IF（G6 =，，CONCATENATE（[，INDIRECT（G6），]，Sheet1，！$ A1）） 。

正在从列中读取excel 文件名 G6 ，然后从 Sheet1 中读取列A 。

这使无效的单元格参考错误。这是否有问题？

解决方案

这是您需要的：

$ pre> = IF（G6 =，，INDIRECT（CONCATENATE（'[，G6，] Sheet1'！$ A1）））


如果 G6 保存文本 zipcodes -phoenixTEST.xlsx ，那么公式相当于

  ='[zipcodes-phoenixTEST.xlsx ] Sheet1'！$ A1 
  

这将产生单元格 A1 的 Sheet1 文件 zipcodes-phoenixTEST.xlsx ...

I have this formula =IF(G6="", "", CONCATENATE("[",INDIRECT(G6),"]","Sheet1","!$A1")).

It is reading excel file name from column G6, and then it read Column A from Sheet1.

This give invalid Cell Reference Error. Is there is something wrong with this?

解决方案

This is what you need:

=IF(G6="", "", INDIRECT(CONCATENATE("'[",G6,"]Sheet1'!$A1")))

If G6 holds the text zipcodes-phoenixTEST.xlsx, then the formula is equivalent to

='[zipcodes-phoenixTEST.xlsx]Sheet1'!$A1

which will yield the value of cell A1 of Sheet1 of file zipcodes-phoenixTEST.xlsx…

这篇关于这个Excel公式有什么问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！