I have the following formula that skips 4 columns when I drag it across a row to paste data. Can someone help me revise it to skip on two columns?
=IF(MOD((COLUMN()-1)/4,1),"",INDEX($Z:$Z,INT((COLUMN()-1)/4)+41))
I have the following formula that skips 4 columns when I drag it across a row to paste data. Can someone help me revise it to skip on two columns?
=IF(MOD((COLUMN()-1)/4,1),"",INDEX($Z:$Z,INT((COLUMN()-1)/4)+41))
Change /4 to /2
FWIW I prefer to use COLUMNS($A1:A1) * to COLUMN()-1 as it is more robust.
The use of COLUMN()-1 in this instance assumes first formula result is always in an odd numbered column which may not prove to be the case over a long period of time and would then generate incorrect results.
*where A1 is the first cell containing the formula
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I tried making the changes but it is not working. Here is my spreadsheet....The range is slightly different then before.
Based on your sample and adapting existing formula
Please Login or Register to view this content.
This is perfect thanks!
In row 5 is there a formula that can remove the column spaces inbetween each ticker in row 4 and leave all of the tickers in one continuos string?
Depends on the volume of "tickers" ... native formulae aren't great when it comes to doing mass concatenation in a single cell calc whereas doing similar via VBA (UDF) is trivial.
If you're happy to use "helper cells" then you can use pretty basic formula to do this - ie use helpers to build an ever extending string and in your result cell simply return the last cell in the helper range.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks