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

 

[PetrickM]

What is my_pointer_ref?

Thank you!
Marcy

 

[GlennUK]

That would be a cell reference containing a pointer to be used in the INDEX formulas, like 3 or 4 or whatever.

 

[PetrickM]

Okay, I think I've got it now...the pointer is only the row number of the data with which I am working on the data sheet, for example 3, not a formula referencing something. My worksheet formulas would go from ='Data Sheet'!D2 to =INDEX('Data Sheet'!D2:AB12,'Attachment F, 2'!P1,18)), where P1 is my pointer cell containing the row number with which I am curently working, and 18 is my column number.

Wouldn't that have the same problem as vlookup if I need to insert new columns; that I would have to update all the subsequesnt column numbers? Instead of inserting an actual column number, is there a way to make that a reference to a column so when I add new columns, the reference follows the original column?

Thank you!
Marcy

 

[GlennUK]

You could point to the column header, like COLUMN('SheetName'!$T$1) instead of hard coding the number.