Trying to mass copy a formula with varying cell references

[wesleyterrill]

Kind of confusing title, sorry about that! Let me elaborate...

I have the following formula in a cell:

=LEFT(INDIRECT("Source!A2"),FIND(" ",INDIRECT("Source!A2"),1)-1) *(Don't worry about what the formula actually does.)*

Basically, I'm trying to drag this cell down about 1000 times for bulk data purposes. BUT... the cell(s) being referenced increase by 3 every time. So, for the next line, it should be A5, then A8, then A11, and so on.

The next 2 formulae should be as follows:

=LEFT(INDIRECT("Source!A5"),FIND(" ",INDIRECT("Source!A5"),1)-1)
=LEFT(INDIRECT("Source!A8"),FIND(" ",INDIRECT("Source!A8"),1)-1)

Whenever I select the first cell (or even the first several cells) and drag down... It only copies the formula(e), it does not update. Does anybody have an idea of how to drag this info down and have it reflect the proper cell? I have finished about 50 lines manually, but I feel like there's a much more efficient way. Thanks for the help!

 

[StephenCrump]

Welcome to the Forum!

On the face of it, you could use:

=LEFT(INDIRECT("Source!A"&2+3*(ROW()-ROW($n:$n))),FIND(" ",INDIRECT("Source!A"&2+3*(ROW()-ROW($n:$n))),1)-1)

where n is the row number of the first cell with this formula.

*(Don't worry about what the formula actually does.)*

But I am curious why you're using INDIRECT, and whether the formula will work if you truncate the reference in this way?

 

[Asala42]

You need to convert the row reference in the quotes (eg "Source!A5") to a formula rather than a constant. One way is to note what row your formula is starting on and use that. For example if your first formula was in row 2, instead of:

=LEFT(INDIRECT("Source!A5"),FIND(" ",INDIRECT("Source!A5"),1)-1)

try

=LEFT(INDIRECT("Source!A"&5+3*(row()-2)),FIND(" ",INDIRECT("Source!A"&5+3*(row()-2)),),1)-1)

So the key there is the 5+3*(row()-2). On row 2 that evaluates to 5+0=5. On row 3 it evaluates to 5+3*(1) = 8, 11,14,17, etc.
If you are starting on a different row you can just change the -2 reference to the start row number.

HTH

 

[wesleyterrill]

Thanks for the response, Stephen. Haha, it's funny you ask, I was looking at the formula with a coworker and even he was like ".....why are you using INDIRECT?" Basically, in cell A2 I have the full name of someone. I am wanting to have the first name in one cell and the last name in another. I did some research and that was a formula I found. It worked initially so I didn't question it. I will try your suggestion and see if it works. Thanks again!

 

[Norie]

Try this.

=LEFT(INDEX(Source!A:A,2+3*(ROW()-2)),FIND(" ",INDEX(Source!A:A,2+3*(ROW()-2)))-1)

 

[StephenCrump]

Thanks for the response, Stephen. Haha, it's funny you ask, I was looking at the formula with a coworker and even he was like ".....why are you using INDIRECT?" Basically, in cell A2 I have the full name of someone. I am wanting to have the first name in one cell and the last name in another. I did some research and that was a formula I found. It worked initially so I didn't question it. I will try your suggestion and see if it works. Thanks again!

Just use Norie's formula. You don't need INDIRECT.

 

[StephenCrump]

I will try your suggestion and see if it works.

Thanks for the PM. I can't respond because your mailbox is full.

Please post follow up questions here, so all can see. Thanks.

 

[wesleyterrill]

Thanks again for your help, guys! I am using the formula that StephenCrump shared but I need to adjust it slightly. This is what my 1st sheet (where I import my data) looks like:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location[/TD]
[TD]Current Title & Company[/TD]
[TD]Project[/TD]
[/TR]
[TR]
[TD]John Mahan[/TD]
[TD]Dallas[/TD]
[TD]Sales manager[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Amazon[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary Thompson[/TD]
[TD]Austin[/TD]
[TD]Sales Engineer[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Shell[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jacob Smith[/TD]
[TD]Houson[/TD]
[TD]Director of Sales[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Basically, this is what my data looks like when I import it to Excel. I need to sort that data into First Name, Last Name, Company, and Job Title. That's where my original formula comes in. The formula for "First Name" did exactly what I needed it to.... But when I inserted that formula into the respective cell, it pulled Mary's name instead of John's, does that make sense? My 2nd sheet (where I need the formulae) looks like this:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Job Title[/TD]
[TD]Company[/TD]
[/TR]
[TR]
[TD]=LEFT(INDIRECT("Source!A"&2+3*(ROW()-ROW($N:$N))),FIND(" ",INDIRECT("Source!A"&2+3*(ROW()-ROW($N:$N))),1)-1)[/TD]
[TD]=TRIM(RIGHT(SUBSTITUTE(INDIRECT("Source!A2")," ",REPT(" ",LEN(INDIRECT("Source!A2")))),LEN(INDIRECT("Source!A2"))))[/TD]
[TD]=INDIRECT("Source!C2")[/TD]
[TD]=INDIRECT("Source!C3")[/TD]
[/TR]
</tbody>[/TABLE]


I am more than willing to drop the INDIRECT aspect of the formula, I'm not even sure what it does. I had someone suggest it at some point and it appeared to work so I kept it. I'll need to adjust the formula to match where it is on the sheet (I'm sure that's an easy fix, I just don't know where to look.) And I'd like to be able to drag all 4 cells down about 1000 times. Thanks again for all of your help, let me know if you need anything additional from me!

 

[StephenCrump]

There are different ways you could rearrange this data set, e.g. some might prefer a VBA solution.

Using your formula approach, and starting with Norie's formula (no need for INDIRECT) for the first column:

A2: =LEFT(INDEX(Source!A:A,2+3*(ROW()-2)),FIND(" ",INDEX(Source!A:A,2+3*(ROW()-2)))-1)
B2: =MID(INDEX(Source!A:A,2+3*(ROW()-2)),LEN(A2)+2,99) (You could TRIM to allow for multiple separating spaces)
C2: =INDEX(Source!B:B,2+3*(ROW()-2))
D2: =INDEX(Source!C:C,2+3*(ROW()-2))
E2: =INDEX(Source!C:C,3+3*(ROW()-2))
F2: =INDEX(Source!D:D,2+3*(ROW()-2))

In my case, I have started all these formulae in Row 2. If you start in another row, adjust all the ROW()-2 references accordingly.

These 2's and 3's:
2+3*(ROW()-2)
3+3*(ROW()-2)

assume the source data (i.e. the John Mahan line) is in row 2, and also should be adjusted if that's not the case.

Excel 2010

	A	B	C	D	E	F
Name	Location	Title	Company	Project
John	Mahan	Dallas	Sales manager	Amazon	Sales
Mary	Thompson	Austin	Sales Engineer	Shell	Sales
Jacob	Smith	Houson	Director of Sales	BP	Sales

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

Results​

Excel 2010

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Name[/TD]
[TD="bgcolor: #FAFAFA"]Location[/TD]
[TD="bgcolor: #FAFAFA"]Current Title & Company[/TD]
[TD="bgcolor: #FAFAFA"]Project[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]John Mahan[/TD]
[TD="bgcolor: #FAFAFA"]Dallas[/TD]
[TD="bgcolor: #FAFAFA"]Sales manager[/TD]
[TD="bgcolor: #FAFAFA"]Sales[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Amazon[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Mary Thompson[/TD]
[TD="bgcolor: #FAFAFA"]Austin[/TD]
[TD="bgcolor: #FAFAFA"]Sales Engineer[/TD]
[TD="bgcolor: #FAFAFA"]Sales[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Shell[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Jacob Smith[/TD]
[TD="bgcolor: #FAFAFA"]Houson[/TD]
[TD="bgcolor: #FAFAFA"]Director of Sales[/TD]
[TD="bgcolor: #FAFAFA"]Sales[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]BP[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

</tbody>

Source​

 

[wesleyterrill]

Perfect, this is exactly what I was looking for. Thank you StephenCrump and Norie for your help! I'll try to stay away from VBA for now... it's a bit above my "expertise" haha!