Copying formulas from irregular cell locations

[pini37]

Hi all,
I’m a new member but have been visiting the forum for a while now. I have low to medium skill levels with Excel due to not regularly using the software causing me to forget useful code and functions.
I need some insight into solving the following:

I have 2 worksheets called “Entry” and “Results”, both of which are in the same workbook.

I want to reference cells that are in Entry to locations in Results
.
The spacing between the columns where the cells are located in Entry are as follows:
Entry!J7, Entry!N7, EntryR7 through to Entry!JK7 (i.e. there are 4 columns separating each location).

The destination cells in Results are in Column alphabetical order (i.e. G10, H10, I10 etc).

If I enter =Entry!J7 in Results and drag it across the row it will naturally not reference Entry!J7, Entry!N7, EntryR7 through to Entry!JK7.

Can someone please provide a formula that can be input in a cell in Results and dragged across the row?

Apologies for length of post and if it is as clear as mud!. Please let me know if more info is required.

Version: 2013
Platform: Window 10


Many thanks in advance.
Pini

[DBY]

Hello Pini welcome to the forum.

One way would be:

Please Login or Register  to view this content.

The Index range would of course need to reference the 'Entry' sheet.

DBY

[jason.b75]

Try

=INDEX(Entry!7:7,(COLUMNS($G10:G10)-1)*4+10)

*4+10 is the key part here, the results are 4 columns apart, with the first result coming from column J, which is the 10th column in the row.

@DBY

It's better to use ROWS() with the cell holding the first formula as the starting point for the row / column count, Using ROW(A1) as you have, will cause the formulas to fail if A1 is removed from the sheet by means of right click / delete.

[DBY]

@ jason
Yes you're correct. I'm truly chastised!

[jason.b75]

I'm truly chastised!

Sounds a bit harsh for a Friday

A trivial bit of info for you, you don't need to specify row 1 in index when there is only 1 row of data, you can use the row as the column, =INDEX(1:1,10) will return J1, even though the 10 is technically row, not column.

[DBY]

Thanks for the heads up on the being able to omit the row argument in this instance. It makes sense now you point it out. Back to the Naughty Step!

[pini37]

Many thanks DBY and Jason for your more than prompt replies! Works a treat!

Creating this spreadsheet as a volunteer for a charity group but I'm starting a new job soon meaning I won't have time to volunteer so really need to finish the work within the next week or so. Now for the next problem!

Thanks again,
Pini