Excel Help - Formula Painter with Referenced Cells

[chibichanman]

Using Excel 2003.

So, I want to reference a cell from another worksheet/excel file.

The specifics of it is that I want to reference every x numbered cell. Example:
=Worksheet1!A3
=Worksheet1!A10
=Worksheet1!A17

However, when I try to drag and auto-fill the other cells, Excel makes a tantrum and outputs:
=Worksheet1!A3
=Worksheet1!A10
=Worksheet1!A17
=Worksheet1!A6
=Worksheet1!A13
=Worksheet1!A20
=Worksheet1!A9
=Worksheet1!A16
=Worksheet1!A23
=Worksheet1!A12
=Worksheet1!A19
=Worksheet1!A26

I want to get it to output 24, 31, 38, etc.

Is there a workaround for this without using macros/VBA?

Thanks in advance!

 

[MrChad]

Have you tried:

=Worksheet1!$A3
=Worksheet1!$A10
=Worksheet1!$A17

 

[chibichanman]

Yep, same result 🙁

 

[PowerEngineer]

Copying formulas doesn't work that way. It's one row per row and/or one column per column only. That's what it's doing when you copy the first three cells down three rows.

Here's one way to get what you want. Fill column A with the row numbers you want (i.e. A1=3,A2=10,A3=17,A4=24,A5=31,A6=38,etc.). Put the following formulas in column B (starting with B1):

=INDIRECT("Worksheet1!"&TEXT($A1,"0"))

Good luck...

 

[mayest]

I'm not sure that I completely understand what you are after. It seems that you want a formula that you can copy down (in a contiguous range) that will reference A3, A10, A17, A24, A31, A38 and so on. If so, then this will do it, but you will need to modify it slightly:

=OFFSET($A$3,7*(ROW()-1)-7*(ROW(C$1)-1),0)

Modifications:

1) Change the $A$3 to include the name of the worksheet (and workbook if this goes in a different workbook).

2) Change the C$1 to the column and row of the first instance of this formula. For example, if you first enter this formula into H21, then it should be H$21. This will anchor the series. When you copy it to H22 then the formula will return the value from A10. When you copy it to H23, it will return the value from A17. And so on.

 

[chibichanman]

mayest:

Wow, that works wonders, but I'm having problems figuring out the specifics of that formula. I put that formula in and for some reason it's adding 8 to the the offset, tried tinkering with a few numbers here and there but was unable to get it down. Any suggestions/tips?

PowerEngineer:

Can't figure out for the life of me the needed format to make that even output anything but REF#, haha. Will give it another stab.

 

[chibichanman]

mayest:

changed formula to
=OFFSET(Sheet2!A3,6*(ROW()-1)-7*(ROW(D$1)-1),0)

the 7 to a 6, and boom, it adds +6 now.

thanks a ton for the assistance!

now to figure out how to get the indirect to work....

 

[chibichanman]

mayest:

I've looked at the formula but can't figure out the math behind it.

the 2nd string, wants to do: 6*(current row - 1) - 7*(this row - 1)?

more importantly, what is row() referring to?

Thank you again in advance!

 

[PowerEngineer]

Originally posted by: chibichanman
Can't figure out for the life of me the needed format to make that even output anything but REF#, haha. Will give it another stab.

I think the problem is that we both forgot that the sheet name needs to be inside single quotes (i.e. ' ). Assuming "Worksheet1" is the name of the worksheet, then the formula should be:

=INDIRECT("'Worksheet1'!"&TEXT($A1,"0"))

I like mayest's approach too; will remember that!

(row() returns the row number of the cell it appears in)