Function not working

Question

Function not working

Sandie Emmerson 20

I am creating a printable register from a source worksheet. The register is set up to include up to 250 members on two sheets. The format of the form is below. The columns where the information is filled in correctly have the format:

='2026 Members'!$I2:$I34

I have made a slight adjustment to the last two columns yet it does not fill these columns. I have tried clearing the existing data from the affected columns and dragging the top row down. Neither work. If I just copy and paste from the previous column it works perfectly - but obviously with the wrong names. I've also tried the following function:

=IF(1=1,'2026 Members'!$I2:$I34)

That doesn't work either.

How can I get this to work

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

The issue may be related to how Excel handles the formula when you adjusted the last two columns. Your original formula:

='2026 Members'!$I2:$I34

references a range of cells rather than a single cell. If the register is designed to pull member names into individual rows, Excel may be treating the formula differently depending on whether the destination cells are part of a dynamic array or a traditional formula range.

If the last two columns need to pull data from different columns in the '2026 Members' worksheet, try using a direct cell reference instead of a range reference. For example, if the first column is pulling names from column I, the next column might use:

='2026 Members'!$J2

Then drag the formula down. If you need the formula to pull a range of names, you can use:

=INDEX('2026 Members'!$J:$J,ROW(A2))

This will return the value from column J corresponding to the row number, and you can drag it down.

If you have adjusted the last two columns, make sure the formulas reference the correct source columns. For example, if the last two columns should pull data from columns K and L, use:

=INDEX('2026 Members'!$K:$K,ROW(A2))

and

=INDEX('2026 Members'!$L:$L,ROW(A2))

The IF(1=1,...) formula does not solve the issue because it simply returns the same range reference.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Sandie Emmerson 20 Reputation points

2026-11-02T22:34:30.1566667+00:00

I thought I'd already responded.

The answer was very simple, changed the format of the affected columns to General from Text. Not sure why the other columns worked. But at least it works now.

Thank you for your reply.

Sandie
Marcin Policht 112.4K Reputation points MVP Volunteer Moderator

2026-11-03T03:00:25.39+00:00

Glad to see. You might want to mark this thread as resolved

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin