来自另一个工作表的Excel间接列，用于行拖动 [英] Excel indirect columns from another sheet for row dragdown

问题描述

我试图拖动一个公式，即

 = IF（SUM（SecurityLevels！E $ 257：E $ 261）> 0，3，IF ！（AND（SUM（SecurityLevels E $ 232：E $ 256）大于0，SUM（SecurityLevels E $ 2：E $ 231），大于0）， 2，IF（SUM（SecurityLevels E $ 2：E $ 231），> 0，1，0）））

目前它不会将列号向下拖动为F，G，H等。我当前的表格I我这样做是ReportOwnerQuery。我试图从下拉列表中的表单是SecurityLevels。手动更改列字母有效但效率极低，因为我必须对JF执行此操作。安全级别表在第1行中有一个名称列表，从列C开始并转到JF。我可以创建一个有助于此的间接，所以我可以将其拖下来更改列字母吗？比查找和替换每一行要快得多。



我尝试过：



查找并替换每一行，它有效，但效率极低。

尝试了间接（）但是我无法正常工作。我让它在SecurityLevels表中工作，用于我正在进行的复杂计算的一部分。以下是一个单元格示例：

 = COUNTIF（INDIRECT（'ReportOwnerQuery'！AD& COLUMN（） -  1），*& $ A260& ;*）

工作手动解决方案：复制粘贴的解决方案将是我正在使用的直到我弄清楚一个公式化的方法是切换到将公式复制到一个空行，将其拖到右边，因为我需要进入公式视图模式（CTRL +`）。将具有不同列的公式复制并粘贴到记事本中，然后粘贴到我想要最初向下拖动的位置。

解决方案

257：E

261）大于0， 3，IF（AND（SUM（SecurityLevelsË

232：E！

I am trying to dragdown a formula which is

=IF(SUM(SecurityLevels!E$257:E$261)>0,"3",IF(AND(SUM(SecurityLevels!E$232:E$256)>0,SUM(SecurityLevels!E$2:E$231)>0),"2",IF(SUM(SecurityLevels!E$2:E$231)>0,"1","0")))

Currently it won't drag down column number to be F, G, H, etc. My current sheet I am doing this in is ReportOwnerQuery. The sheet I'm trying to dragdown from is SecurityLevels. Manually changing the Column letter works but is super inefficient as I have to do it to JF. The Security levels sheet has a list of names in Row 1 starting in Column C and going to JF. Could I make an Indirect that will help with this so I can just drag it down to change column letter? Will be a lot quicker than doing Find and Replace every line.

What I have tried:

Find and Replace every line, it works but is super inefficient.
Tried a Indirect() but I can't get it working quite properly. I got it working in the SecurityLevels sheet for part of the complex calculation I was doing. Here is one cells example:

=COUNTIF(INDIRECT("'ReportOwnerQuery'!AD"&COLUMN()-1),"*"&$A260&"*")

Working manual solution: A solution from copy pasting would be what I am using until I figure out a formulated way was to switch to copy the formula to a an empty row, drag it to the right as much as I needed the go into Formula view mode (CTRL+`). Copy and paste the formulas with columns different into Notepad then paste where I wanted to originally drag down.

解决方案

257:E

261)>0,"3",IF(AND(SUM(SecurityLevels!E

232:E

这篇关于来自另一个工作表的Excel间接列，用于行拖动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！