Deleting cells wrecks formulas

[Coop913]

Hi -

I have a workbook with two worksheets. My second worksheet, "Scoresheet", has many cells that grab information from cells in the first worksheet, "Lineups". All of the information that is grabbed from "Lineups" is in row 2. I would like to be able to delete that row 2 (after printing "Lineups") and have "Scoresheet" update and grab information from the new row 2 in "Lineups". I would then continue this process, continually erasing that row two, having the other rows move up, and so on.

This is not working, as I just get #REF! error messages. Seems like an easy thing to do, but I can't figure it out. Can anyone please help me? Thanks in advance.

Jack

 

[T. Valko]

Hi -

I have a workbook with two worksheets. My second worksheet, "Scoresheet", has many cells that grab information from cells in the first worksheet, "Lineups". All of the information that is grabbed from "Lineups" is in row 2. I would like to be able to delete that row 2 (after printing "Lineups") and have "Scoresheet" update and grab information from the new row 2 in "Lineups". I would then continue this process, continually erasing that row two, having the other rows move up, and so on.

This is not working, as I just get #REF! error messages. Seems like an easy thing to do, but I can't figure it out. Can anyone please help me? Thanks in advance.

Jack

What does your formula look like?

=SUM(INDIRECT("Lineups!A2:J2"))

When using INDIRECT that formula will ALWAYS point to Lineups!A2:J2.

 

[Cindy Ellis]

Hello Jack, and Welcome to the MrExcel board!

Although there may be other ways, I think the simplest would be to use the "Indirect" function. Replace the direct cell reference with

INDIRECT("Lineups![COLOR=red]C2[/COLOR]")

where C2 is replaced with the specific cell reference that you want to point to. If there's no other formula already in the cell, you would put "=" before the Indirect function.

Hope that helps,
Cindy

 

[mikerickson]

Another way would be to:

Press Record Macro
Select row 3:100 (or some other large number)
Cut
Select row 2
Paste
Stop recording.

then Run that macro instead of deleting row 2.

 

[Coop913]

Thanks to each of you! I have all the fields working correctly except for the following one.

If Lineups!$Y$2 equals 1, I want this cell on "Scoresheets" to show "Lineups!$X$2". If not, then show nothing. (Again, I will need the INDIRECT function to be involved because I will be deleting row two as before.) I thought the following formula might have the general idea, but it's not working, as it issues only an error.

=IF(INDIRECT("Lineups!$Y$2")=1,(INDIRECT("Lineups!$X$2), """)))

Thanks again, Jack

 

[T. Valko]

Thanks to each of you! I have all the fields working correctly except for the following one.

If Lineups!$Y$2 equals 1, I want this cell on "Scoresheets" to show "Lineups!$X$2". If not, then show nothing. (Again, I will need the INDIRECT function to be involved because I will be deleting row two as before.) I thought the following formula might have the general idea, but it's not working, as it issues only an error.

=IF(INDIRECT("Lineups!$Y$2")=1,(INDIRECT("Lineups!$X$2), """)))

Thanks again, Jack

Try it like this...

=IF(INDIRECT("Lineups!Y2")=1,INDIRECT("Lineups!X2),"")

You don't need the $ signs!

 

[Coop913]

Not working. Excel wants to add a close parent at the end, which seems wrong, and that doesn't work anyway.

Is there a way to attach my workbook? I don't see one.

 

[Cindy Ellis]

It's just missing a quote:
Should be

=IF(INDIRECT("Lineups!Y2")=1,INDIRECT("Lineups!X2[COLOR=red][B]"[/B][/COLOR]),"")

(missing quote highlighted in red).
Hope that helps,
Cindy

 

[T. Valko]

Try it like this...

=IF(INDIRECT("Lineups!Y2")=1,INDIRECT("Lineups!X2),"")

You don't need the $ signs!

Not working. Excel wants to add a close parent at the end, which seems wrong, and that doesn't work anyway.

Is there a way to attach my workbook? I don't see one.

Ooops!

My fault!

I forgot the closing quote around Lineups!X2.

Try this one:

=IF(INDIRECT("Lineups!Y2")=1,INDIRECT("Lineups!X2"),"")

 

[Coop913]

That did it! Thanks much to both of you!

Jack