需要修改这个基于另一个单元格内容显示数据的公式 [英] Need to revise this formula that displays data based on another cell's contents

问题描述

我无法让这个公式正常工作.这是我需要做的(见下图):

I can't get this formula working properly. Here is what I need to do (see included image below):

我需要根据单元格 C9 和 C10(付款条件)中的数字将第 9-10 行(销售额)中的数据显示/复制到第 28-29 行(集合)中.例如，如果 1 月 5 日星期二(单元格 F9)的 GM 销售额为 1,900 美元，付款期限为 10 天(单元格 C9)，那么我需要在 1 月 19 日星期二(单元格 P28)的集合部分中显示 1,900 美元(第 28-29 行)，自销售之日起 10 个工作日.这有意义吗?这是我现在使用的公式(从单元格 E28 开始并向右拖动):

I need the data in rows 9-10 (sales) to be displayed/copied in rows 28-29 (collections) based on the number in cells C9 and C10 (payment terms). For example, if there are $1,900 in GM sales on Tuesday, Jan 5 (cell F9) and payment terms are 10 days (cell C9), then I need $1,900 displayed on Tuesday, Jan 19 (cell P28), in the collections section (rows 28-29), 10 business days from when the sales were made. Does this make sense? Here is the formula I am using now (starting in cell E28 and being dragged to the right):

=IF(COLUMN() - E9 <1, 0, INDIRECT(ADDRESS(9, COLUMN() - $C$9)))

然而，它并没有完全工作.它将不应包含在数据表中的数据标签移动到数据表的左侧.应该只移动从单元格 E9 开始的销售数据.您可以在下图中的第 28 行(J-N 列)中看到此问题.

However, it is not working completely. It is moving the data labels to the left of the data table that are not supposed to be included in the data table. Only sales data starting in cell E9 are supposed to be moved. You can see this issue in row 28 (columns J-N) in the image below.

此公式还必须适用于 1 天到 10 天的付款条件.因此，福特在 1 月 4 日星期一(单元格 F10)的销售额为 5,000 美元，付款期限为 3 天(单元格 C10)，需要在 1 月 8 日星期五(单元格 I29)显示.

This formula also has to work with payment terms from 1 day - 10 days. So, the $5,000 in Ford sales on Monday, Jan 4 (cell F10), with payment terms of 3 days (cell C10), needs to be displayed on Friday, Jan 8 (cell I29).

如果您需要任何其他详细信息来解决此问题，请告诉我.谢谢！

Let me know if you need any additional details to solve this issue. Thanks!

推荐答案

这是您要找的吗?=IFERROR(INDEX($E$9:$X$13,MATCH($B28,$B$9:$B$13,0),MATCH(WORKDAY(E$5,-INDEX($C$9:$C$13,匹配($B28,$B$9:$B$13,0))),$E$5:$X$5,0)),0)

该公式对销售额进行索引，并在结果部分中销售中提及的客户名称等于客户的行中显示金额，结果部分中的列中结果日期等于销售中提及的日期减去后面提及的工作日数销售部分的客户名称.

The formula indexes the sales amounts and shows the amount in the row where the customer name mentioned at sales equals the customer at the result section with the column where the result date equals the date mentioned at sales minus the number of workdays mentioned behind the customer name at the sales section.

这篇关于需要修改这个基于另一个单元格内容显示数据的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！