Find & Replace Using Wildcard in Cell Reference

[PetrickM]

Good afternoon! I have monthly reports to prepare that require a new sheet of data with lots of formulas to be created from a section of data in another spreadsheet. For example - Datasheet contains the information in columns G-AG, rows 1-300. Worksheet 1 has formulas linked to datasheet row 1, columns G-AG. Worksheet 2 has formulas linked to datasheet row 2, colums G-AG. I have to create new worksheets for each new row of data and link it to the appropriate cells in the new row.

I usually copy the last worksheet created, and manually change the cell references which, by new row 10, is excruciating.

Is there a way to use a wildcard in Find and Replace, like:

Find - 'Data Sheet'!?3
Replace - 'Data Sheet'!?4

...where the question-mark-replaced item would remain the same? That way I wouldn't have to manually change all the rows - 'Data Sheet'!D3 to 'Data Sheet'!D4, 'Data Sheet'!AB3 to 'Data Sheet'AB4. I might not make it to row 300 )

Thanks for your help!
Marcy

 

[GlennUK]

Do you have other numbers in your formulas? If not, just do a replace of the row numbers ... like replace 3 with 4, Replace All.

If you do have numbers in your formulas, which prevent you using Edit/Replace, you may want to redesign your formulas to fetch the data from the Datasheet using the INDEX function, and a pointer for each sheet.

 

[PetrickM]

Good morning, Glenn! I do have other numbers in my formulas that do not get changed, which complicates matters. I am not very familiar with the index function and can't picture how that would work. Could you give a little more detail?

Thank you!
Marcy

 

[GlennUK]

You use INDEX like this:

=INDEX('Data Sheet'!$D$1:$D$999,mypointer)

If you build a reporting block like this, then you'd just point at a new row in the source data by changing the pointer.

 

[PetrickM]

That sounds like a good possibility. I would keep the reference the new row in my first formula, which contains, for example, the company name, then set up the index formulas in the subsequent locations and point them towards the first formula?

Thank you!
Marcy

 

[PetrickM]

Someone else in my office suggested using vlookup. Would there be an advantage to using index versus vlookup or vice versa?

Thank you!
Marcy

 

[PetrickM]

Ah yes, now I remember why I didn't use vlookup - if I need to add columns on my datasheet, I have to redirect references to columns beyond the new column. In your index formula, I don't understand the pointer. When using index, as in =INDEX('Data Sheet'!D2:AB12,3,19), wouldn't I still have to manually update the row number?

Sorry to post so much - I have to prepare the reports for tomorrow and was hoping to find a more efficient way of generating them before then.

Thank you!
Marcy

 

[GlennUK]

First you say that you'd keep the reference to the new row in a cell, then you ask "wouldn't I still have to manually update the row number" in the formula ... no, you'd use the pointer to the row that you have in a cell elsewhere, for ALL the index formulas.

Yes, you could use VLOOKUP, except you said that you wanted to point to a particular row with formulas, and not lookup data. So use the appropriate function for whatever action you require.

 

[PetrickM]

In reading your response and not being familiar with INDEX, I think I may have prepared the index fomula wrong:

=INDEX('Data Sheet'!D2:AB12,3,19)

is what returned the correct data from the datasheet but doesn't reference the new data in cell C4. From what I've been able to read online about the index fomula, it looks at an area and returns the data in the cell referenced, in the case of my formula three rows down and 19 columns over. As I said, I don't understand where the pointer comes in. If you could please provide more detail, I would be grateful.

I'm not trying to cause difficulty; just trying to understand )

Thank you!
Marcy

 

[GlennUK]

What I'm saying is that instead of having:
'Data Sheet'!D3

and then altering the formula by hand for the next item ( 'Data Sheet'!D4 ), have
=INDEX('Data Sheet'!$D$1:$D$999, my_pointer_ref)

and alter the contents of whatever is in the cell my_pointer_ref.