Help Copying Inderect Formula

[leehowardblair]

Here is the formula I am using on sheet HOME at cell A1:

=INDIRECT("'"&'EVENT INFORMATION'!$C$10&"'!A1")

I am using a user input on the sheet EVENT INFORMATION in cell C10 to then return the information from the selected sheet's cell A1.

I want to be able to copy this formula to the whole sheet, so that I am basically changing the contents of the sheet HOME to match that of the sheet that the user inputs on the EVENT INFORMATION sheet. I need to do this, because I have a 3rd part program that links to this information, but will always link to the HOME sheet, regardless of what sheet the user actually selects.

Right now, if i copy/paste or drag the formula, I keep getting the exact same formula. The A1 is not changing.

Thanks for your help in advance!

 

[T. Valko]

Here is the formula I am using on sheet HOME at cell A1:

=INDIRECT("'"&'EVENT INFORMATION'!$C$10&"'!A1")

I am using a user input on the sheet EVENT INFORMATION in cell C10 to then return the information from the selected sheet's cell A1.

I want to be able to copy this formula to the whole sheet, so that I am basically changing the contents of the sheet HOME to match that of the sheet that the user inputs on the EVENT INFORMATION sheet. I need to do this, because I have a 3rd part program that links to this information, but will always link to the HOME sheet, regardless of what sheet the user actually selects.

Right now, if i copy/paste or drag the formula, I keep getting the exact same formula. The A1 is not changing.

Thanks for your help in advance!

This formula entered in cell A1:

=INDIRECT("'"&'EVENT INFORMATION'!$C$10&"'!"&ADDRESS(ROWS(A$1:A1),COLUMNS($A1:A1)))

Then drag copy in any direction.

 

[leehowardblair]

This formula entered in cell A1:

=INDIRECT("'"&'EVENT INFORMATION'!$C$10&"'!"&ADDRESS(ROWS(A$1:A1),COLUMNS($A1:A1)))

Then drag copy in any direction.

You ARE MY HERO!

Thanks so much!

 

[leehowardblair]

Do you know if there is a way to adjust the formula to not return a 0 if there is nothing in that cell?

I know of ISBLANK, but I am not sure how to incorporate it into this formula with so much going on already!

 

[T. Valko]

Do you know if there is a way to adjust the formula to not return a 0 if there is nothing in that cell?

I know of ISBLANK, but I am not sure how to incorporate it into this formula with so much going on already!

It'll make the formula twice as long.

=IF(INDIRECT("'"&'EVENT INFORMATION'!$C$10&"'!"&ADDRESS(ROWS(A$1:A1),COLUMNS($A1:A1)))="","",INDIRECT("'"&'EVENT INFORMATION'!$C$10&"'!"&ADDRESS(ROWS(A$1:A1),COLUMNS($A1:A1))))

 

[leehowardblair]

That makes sense...

Thanks very much!

 

[T. Valko]

That makes sense...

Thanks very much!

You're welcome!

 

[jasonb75]

Based on the information given try

=INDIRECT("'"&'EVENT INFORMATION'!$C$10&"'!RC",0)&""

This will blank empty cells, but it formats numbers (including dates) as text.

 

[T. Valko]

Based on the information given try

=INDIRECT("'"&'EVENT INFORMATION'!$C$10&"'!RC",0)&""

This will blank empty cells, but it formats numbers (including dates) as text.

I like most of that as long as there is no numeric data. Good thinking!