Forum Discussion
Skipping cells in formula
Hi,
I have made an excel file where I have data in 'blocks', not a list. I want to make a summary tab, but I can't find a way to make the formula in a way where I can put in 2 items and then 'pull' the green box around the selected cells so it fills in the formula in the following cells automatically. For example:
I want to make a summary of the values in the boxes, in a list. So i want the values from the boxes to be in A2,A3A4A5A6, etc. but the values from the boxes are in: L17, W17, L41, W41, L65, W65, and so on. Is it possible to add those values in a list? Its a lot of data so i can't really do it manually.
another, easier example could be:
i've put it as "(CELL)value" example: (A2)5
How i want the summary to look is like this:
(A2)=L17
(A3)=W17
(A4)=L41
(A5)=W41
(A6)=L65
(A7)=W65
Is it possible to make a formula that fills in automatically till cell 500 for example?
Thank you in advance,
Luuk Elsten
Hi LuukElsten,
you can use the following formula:=INDEX(L:L, (ROW(A2) - 2) * 3 + 17)This formula will return the value from column L, starting at row 17 and skipping every other row. So, in cell A2, the formula will return the value from cell L17, in cell A3, it will skip a row and return the value
from cell L41, and so on.This formula is equivalent to the OFFSET formula that I provided in my previous response. However, the INDEX function is generally considered to be more efficient than the OFFSET function, especially when working with large datasets.
To use the INDEX function, simply copy and paste it into cell A2 and then copy it down to cell A500 (or however many cells you need). Excel will automatically fill in the correct values for each row.
Here is an example of how to use the formula:
Cell | Formula | Result ------- | -------- | -------- A2 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L17 A3 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L41 A4 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L65 A5 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L89 A6 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L11
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
(LinkedIn)
1 Reply
Hi LuukElsten,
you can use the following formula:=INDEX(L:L, (ROW(A2) - 2) * 3 + 17)This formula will return the value from column L, starting at row 17 and skipping every other row. So, in cell A2, the formula will return the value from cell L17, in cell A3, it will skip a row and return the value
from cell L41, and so on.This formula is equivalent to the OFFSET formula that I provided in my previous response. However, the INDEX function is generally considered to be more efficient than the OFFSET function, especially when working with large datasets.
To use the INDEX function, simply copy and paste it into cell A2 and then copy it down to cell A500 (or however many cells you need). Excel will automatically fill in the correct values for each row.
Here is an example of how to use the formula:
Cell | Formula | Result ------- | -------- | -------- A2 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L17 A3 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L41 A4 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L65 A5 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L89 A6 | =INDEX(L:L, (ROW(A1)-2)*3 + 17) | L11
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
(LinkedIn)