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

Marcin Policht 112.4K MVP Volunteer Moderator

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

1 additional answer

Your answer

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

Answer 1

='2026 Members'!$I2:$I34 is not a valid single-cell fill formula. It refers to a range, not one row-specific cell, so dragging it down will not populate row by row as expected.

Use a row-based reference in the first cell instead, for example:

='2026 Members'!I2

Then drag the fill handle down. Excel will use relative references and change it to:

='2026 Members'!I2
='2026 Members'!I3
='2026 Members'!I4

If part of the reference must stay fixed, use absolute references with $. For example:

='2026 Members'!$I2 keeps column I fixed while the row changes when filled down.
='2026 Members'!$I$2 keeps both the column and row fixed, so every filled cell points to the same source cell.

For the last two columns that are not filling:

Check the first formula in those columns and make sure it points to a single cell, not a range.
If the source is in column I and should fill downward, start with ='2026 Members'!$I2.
Drag down using the fill handle.
If the fill handle does nothing, in Excel go to Preferences > Edit and make sure Allow fill handle and cell dragging and dropping is enabled.
If formulas still do not update after filling, go to Preferences > Calculation and ensure Automatic is selected.
If the formula is showing instead of the value, change the destination cells to General instead of Text, then edit the cell and press Enter so Excel recalculates it.

=IF(1=1,'2026 Members'!$I2:$I34) does not fix the issue because it still returns a range reference instead of a normal row-by-row cell reference.