Drag dwon formula increment cel value larger then 1

Zand

New Member
Joined
Nov 13, 2006
Messages
19
Hello,

I have a simple problem that I cannot get to work...

Problem
I have one list (vertical in one column) with data.
I want to create a second list (vertical one column) with a drag down formula that takes only every (for example) 3th value from the 1e list.

What did I do to solve it
I have searched the web many times found lots of basic drag down explanations (yes I know that 10-20... becomes 10-20-30-40 etc.) and found two solution that come up most.
That is OFFSET($A$3,(ROW()-1)*3,0) and I found solutions that use INDEX and COUNTA for 1e lists that are not fix but can grow longer.

I cannot get any to work. I must say I use a Dutch version but Translator solves that problem.
I do encounter this often, where I wish I could make a simple formule for this "problem".
In stead of labor intensive selecting cells piece by piece, I just want to drag down a formule, no selection errors and for long 1e lists, this is not nice to do.

Does anybody have a smart idea?
I like the fix with the OFFSET command.

Oh yes, in all the examples I found the 1e list is starting at A1... this does not have to be that way. My 1e list is starting at B5 (could be any other value but A1).

Thanks in advance!

Greetings, Pim
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Zand

New Member
Joined
Nov 13, 2006
Messages
19
Hello Fluff,

You must think I am ungreatfull not to have answered sooner... I just was not getting to it earlier.

The XLBB Tool is great, copied formula and changed ROWS to the local RIJEN and figured out what it did.
What is column D doing?! Ah I got it, nothing, it just count the ROWS for the counter!

I did have to change the "," into a ";" as a divider for the Arguments in de Formula but then in works nicely.

I have to look into functions like INDEX and ROWS beter, simple and elegant solution. Much simpeler then stuff I saw online.

So many thanks!

Greetings, Pim
(Zandvoort NL)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
The D$5:D5 should really be C$5:C5 for safety sakes, it was a typo on my part.
 

Zand

New Member
Joined
Nov 13, 2006
Messages
19
I was just thinking of trying that out and indeed it works also in the same column.
Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
My pleasure.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,253
Messages
5,600,544
Members
414,387
Latest member
Vincent88

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
Top