Office & Productivity Software forum


Fill columns with alternate formula

I'm creating a summary sheet to pick certain figures off a large worksheet. I need the last value of every 6th and 7th column and I finally figured out the formulas (the first columns are I and J)

INDEX(sheet!23:23,COLUMNS($A1:A1)*6+3) and

However, if I drag this to the right, the next columns will be the 12th and 13th columns because of each formula skipping a cell. Is there a way to make the formula ignore the skipped cell. Or is there a better formula I can use?

Thanks in advance.

Discussion is locked
You are posting a reply to: Fill columns with alternate formula
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Fill columns with alternate formula
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.

All Answers

Collapse -
Use these formulas instead

In reply to: Fill columns with alternate formula

Hi, here are the two formulas you could use instead:

=INDEX(Sheet!23:23,IF(COLUMN() = 1, 6, COLUMN()*3 + 3) + 3)


=INDEX(Sheet!23:23,IF(COLUMN() = 2, 7, COLUMN()*3 + 1) + 3)

Tumbling Wave

Collapse -
Didn't work

In reply to: Use these formulas instead

I had to edit the formula because my summary sheet does not start at column A. It starts at column B. And when I dragged the formula to the right, I didn't get the expected result.

I had a little trouble following the formula so I couldn't adjust it. I'll look at it again later.

Collapse -
detail request

In reply to: Didn't work

I may be making wrong assumptions. Those formulas were meant to be put in cells A1 and B1. Then, to copy, you would select both cells, then hover over the lower-right of that select, then drag to the right. It also assumes the data it will be pulling is from a worksheet called "Sheet". In "Sheet", it assumes and the first instance of the "6th" column is Column I and the first instance of the "7th" column is Column J. That means the assumption that all the data in "Sheet" starts at column D. If any of this is untrue, please clarify or send me screenshots in a message, I think this can be solved. In your last post, if summary starts at B (rather than A), then I assume you mean the first formula should be in column B1 and the next formula should be C1. If that's the case, then the formula should be updated as follows:

=INDEX(Sheet!23:23,IF(COLUMN() = 2, 6, (COLUMN() - 1)*3 + 3) + 3)


=INDEX(Sheet!23:23,IF(COLUMN() = 3, 7, (COLUMN() - 1)*3 + 1) + 3)

Collapse -
Not untrue

In reply to: detail request

Thank you for your help. You are right on point.

In the summary sheet, I'm starting from column B and C. In the worksheet "Sheet", I'm picking from columns I and J, O and P, U and V etc. Your formula has a lot of additions to it, can you break it down?

I amended the previous formulas to

=INDEX(Sheet!23:23,IF(COLUMN() = 2 6, COLUMN()*3 + 3) + 3)


=INDEX(Sheet!23:23,IF(COLUMN() = 3, 7, COLUMN()*3 + 1) + 3)

which gave me I and J but nothing else.

I used the new ones and while I, O, U etc filled in correctly, the second one one didn't.

Collapse -
Compact Solution

In reply to: Not untrue

The following are more concise versions of the formulas. In cell B1, you could have a formula like:

=INDEX(Sheet!23:23,COLUMN()*3 + 3)

and in cell C1 you could have a formula like:

=INDEX(Sheet!23:23,COLUMN()*3 + 1)

The only direction I tried to drag those formulas is to the right. This gave me I23 and J23, O23 and P23 etc. Why you never got J, P, V to show I'm not sure. My second formula assumes it is being placed in columns C, E, G etc.

Collapse -

In reply to: Compact Solution

Thank you. It worked . The previous formulas were hard to follow but this was simple and easy to understand.

Thanks once again.

Popular Forums

Computer Newbies 10,686 discussions
Computer Help 54,365 discussions
Laptops 21,181 discussions
Networking & Wireless 16,313 discussions
Phones 17,137 discussions
Security 31,287 discussions
TVs & Home Theaters 22,101 discussions
Windows 7 8,164 discussions
Windows 10 2,657 discussions


Sublime suburban chariot

High on style and technology, the 2019 Volvo XC90 is an incredibly satisfying everyday crossover.