Copying Formala across a columns in a spreadsheet usinga patern

RMadden

New Member
Joined
May 30, 2022
Messages
15
Office Version
  1. 2021
Platform
  1. Windows
I have a formula in cell B7 being =IF(SUM(DataInput!M2*1)>0,DataInput!M2+0,"")), now I want to copy this formula across a range of columns but increasing the cell references by 4 each column across for example cell C7 should by =IF(SUM(DataInput!Q2*1)>0,DataInput!Q2+0,"") . As a side issue the "*1" and "+0" part of the equation was needed for excel to recognise the results as a number
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
How about
Excel Formula:
=LET(a,INDEX(DataInput!$M$2:$AZ$2,,COLUMNS($B7:B7)*4-3)*1,IF(ISERROR(a),"",IF(a>0,a,"")))
 
Upvote 0
Thanks Fluff, I used your formula and it came up with a #NAME? error. When I look at the function arguments the #name? error reports as being associated with a, also giving a #Value error on the IF(ISERROR section of the code.
FunctionArgument.png
 
Upvote 0
Post #2 formula works for me.
Looks like you have modified it a bit - can you post your full modified formula?
 
Upvote 0
=LET(a,INDEX(DataInput!$M$2:$AZ$2,,COLUMNS($B7:B7)*4-3)*1,IF(ISERROR(a),"",IF(a>0,a,"")))

I only moved the range from $M$2:$AZ$2 to $M$2:$ABS$2 - but it still recalls the #NAME? error when i used the exact code. My DataInput Sheet does range from Column A to Column ABS. I wonder whether the problem is the source number format in the DataInput Sheet is in fact "General" - the numbers are imported from another workbook using vba code - see attached code picture

Screenshot.png
 

Attachments

  • InputLastRow.png
    InputLastRow.png
    27.6 KB · Views: 4
Upvote 0
What happens if you try this formula in B7?
Excel Formula:
=IF(ISERROR(INDEX(DataInput!$M$2:$ABS$2,,COLUMNS($B7:B7)*4-3)*1),"",IF(INDEX(DataInput!$M$2:$ABS$2,,COLUMNS($B7:B7)*4-3)*1>0,INDEX(DataInput!$M$2:$ABS$2,,COLUMNS($B7:B7)*4-3)*1,""))

Could we see 5-10 columns of row 2 starting at column M of the DataInput sheet?
Better still, could we have that, and the part of the formula sheet shown above, with XL2BB instead of a picture so that we can test with your data?

BTW, I'm not sure it relates to this problem but I suspect your vba code is not quite doing what you expect.
Suppose lCopyLastRow = 1000 then this line ...

1662174978838.png


... will be copying not down to row 1,000 but down to row 21,000 (A2:ABD21000)
 
Upvote 0
Solution
In addition to Peter's comments, if you type =LE into a cell do you get the LET option?
 
Upvote 0
In addition to Peter's comments, if you type =LE into a cell do you get the LET option?
If the OP does not have LET, they wouldn't get/be able to produce the image in post #3 would they?
 
Upvote 0
Yes they would, I would have expected to see the argument names to the left of the dialogue box so I changed the name to =LETA & clicked on the fx symbol & got exactly what the OP displayed.
 
Upvote 0
OK, yes, I think you are probably on to it. (y)
If so, then hopefully the full version in post #6 will work for them. (.. or they simply apply updates to get LET & use your formula)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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