Solved How can I increment a formula by 1 for a group of formulas

February 29, 2016 at 12:39:26
Specs: Macintosh
Currently what is happening is that Im attempting to increment the formula +1. for example here is what is in b1-b4:
Cell | Formula
b1 | =(mark!A8)& " - "& (Config!A4)
b2 | =(mark!A8)& " - "& (Config!A4)
b3 | =(mark!A8)& " - "& (Config!A4)
b4 | =(mark!A8)& " - "& (Config!A4)

if I select all 4 rows and paste it directly below "b4" it gives me this:
b5 | =(mark!A13)& " - "& (Config!A4)
b6 | =(mark!A13)& " - "& (Config!A4)
b7 | =(mark!A13)& " - "& (Config!A4)
b8 | =(mark!A13)& " - "& (Config!A4)

when I really want this:

b5 | =(mark!A9)& " - "& (Config!A4)
b6 | =(mark!A9)& " - "& (Config!A4)
b7 | =(mark!A9)& " - "& (Config!A4)
b8 | =(mark!A9)& " - "& (Config!A4)

How can I accomplish this?


See More: How can I increment a formula by 1 for a group of formulas

Report •


✔ Best Answer
March 1, 2016 at 09:58:25
Following along with DerbyDad03s comments, would not the =FLOOR() function work just as well as =FLOOR.MATH, and be more universal across versions and platforms?

Something like:

=(OFFSET(Mark!A$8,FLOOR(((ROW()-1)/11),1),0)&" - "&(Config!A5))

I'm using 2007, so both =FLOOR.MATH and =FLOOR.PRECISE are
unavailable to me.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
February 29, 2016 at 14:07:31
Don't take this the wrong way, but I don't believe you, ;-)

If I paste the formulas from B1:B4 in to B5:B8, I get this:

=(mark!A12)& " - "& (Config!A8)

Both references increment by 4. In your example, you are incrementing one reference (A8) by 5 and not incrementing the other one (A4) at all. I don't see how that could happen.

So, it this what you want or do you want to lock in the A4 reference and not have it change?

=(mark!A9)& " - "& (Config!A5)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 29, 2016 at 15:55:01
I had some time, so I'll offer a couple of suggestions. Let us know if either of these work for you.

If you want to increment the mark!A reference by 1 every 4 cells and lock the Config!A4 reference, put this in B1 and drag it down:

=INDIRECT("mark!A" & (INT((ROW(A1)-ROW(A$1))/4)*1)+8) & " - "& Config!$A$4


If you want to increment the mark!A reference by 1 every 4 cells and also increment the Config!A reference by 1 every 4 cells, put this in B1 and drag it down:

=INDIRECT("mark!A" & (INT((ROW(A1)-ROW(A$1))/4)*1)+8) & " - "&
INDIRECT("Config!A" & (INT((ROW(A1)-ROW(A$1))/4)*1)+4)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
March 1, 2016 at 06:25:32
Thanks Derby, we're using the mac version of excel so things have been confusing all around, weird enough the (Config!A4) never changes when we copy and paste and yes we miscounted (should have been a 12) . but we got it fixed with this code:

=(OFFSET(MARK!A$8,FLOOR.MATH((ROW()-1)/11),0))& " - "& (Config!A5)

This way the cell number changes every 10 rows. The reason I needed it was because Im creating a mass Magento Products import for simple Products, The formulas Populate certain fields (name, size, color,) the reason for the 11 (in my case multiples of ten) is to copy each set of the sizes to a different row and have it change in value for every paste, this saves time for me instead of having to go cell by cell and modify them.


Report •

Related Solutions

#4
March 1, 2016 at 07:19:00
I'm glad you found a solution that works for you.

It can get really confusing (and annoying) when certain functions only work on certain platforms and/or specific versions. For example, the FLOOR.MATH function is apparently only available as follows:

Excel 2016, Excel 2013, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel for Android phones

I have 2013 for Windows at home and 2010 for Windows at work. That means that I probably couldn't use your workbook at work. (I was unable to test your solution on my 2010 version) That might be something to keep in mind if people with other platforms/versions need to use your workbook.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
March 1, 2016 at 09:58:25
✔ Best Answer
Following along with DerbyDad03s comments, would not the =FLOOR() function work just as well as =FLOOR.MATH, and be more universal across versions and platforms?

Something like:

=(OFFSET(Mark!A$8,FLOOR(((ROW()-1)/11),1),0)&" - "&(Config!A5))

I'm using 2007, so both =FLOOR.MATH and =FLOOR.PRECISE are
unavailable to me.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •


Ask Question