Cross-referencing formula to leave the cell blank until its ready to be populated.

[Vic78]

Hi,

I would like to find a formua for Excel 2003 that involves Cross-Referencing within the sheets of a spreadsheet.

I have taken information from the 1st sheet and copied it onto the 2nd sheet and would like the formula to cross-reference the data so the same is read in each when the main 1st sheet has a Y in it to say it's completed. I have worked out a simple formula to copy the cell from sheet 1 to sheet 2 (whether its blank or has a Y in it):

='sheet1'!H2501

but this leaves a 0 in the cell for sheet 2 instead of a blank cell as in sheet 1 when the work has not been complete. I wouldnt mind this so much but I am counting the number of Y in each row with a formula further down the page and since sheet 1 has the same formula as sheet 2

=COUNTIF(B3:B2501,"Y") (H3:H2501,"Y" in sheet 1)

it has started counting the 0 instead of counting the letter Y, I need blank cells in sheet 2 so I get the same outcome calclations as sheet 1.

I hope this explains what I need as I am guessing it is abit more involved than

='sheet1'!H2501

Thanking in antcipation!

[peterjuhnke]

Please attach a dummy sheet so we can see what you can.
Regards
Peter

[Cutter]

It sounds like you just need to change your simple formula to:

Please Login or Register  to view this content.

[Vic78]

Hi,

Hope this helps, Im not the best at explaining things im unsure of.

Kind regards

[Cutter]

On Sheet 2 in B2 put this formula:

Please Login or Register  to view this content.

and copy down to B8

In Sheet 2 B11 put this formula:

Please Login or Register  to view this content.

What is it that you want in Sheet 1 cell I8?

[Vic78]

I just wanted to cross-reference the cells in I8 and B8 to read the same, showing either a Y or a blank cell so that my other formulas can count the blank and Y cells.

[Cutter]

That's what I was afraid of. You can't have 2 cells refer to each other - that would create a circular reference.

You could have a third cell watch those two cells and tell you if they are holding equal values but you can't have two cells watch each other.

But in this case your B cells on Sheet 2 will always equal your I cells on Sheet 1 because that's what the formulas on Sheet 2 are doing.

[Vic78]

Aww no!

I want sheet 2 to have a formula to mimick the information of data (whether it be a Y or blank) in the Main spreadsheet (sheet 1). But in the sheet 2, it states a zero 0 and not a blank cell, which is what I would like. I think i would only need a formula in sheet 2 as I would be entering all the data in the main sheet 1.

Thank you so much for helping!

[Cutter]

Your header for Col I suggests that an 'N' might appear as opposed to a 'Y' or a blank. You should probably change the formula for Sheet 1 I13 to:

Please Login or Register  to view this content.

also, on Sheet 2 B11 same thinking

Please Login or Register  to view this content.

[Vic78]

Opps, well noted, I forgot to delete the Y/N in the questions. I did origionally have a formula for "Y", "N" and 'To complete' but thought it would be easier to read the rows if they only had a "Y" or a blank cell, so I deleted the "Not Complete "N" row and left the 'Completed Y' and 'To complete - blank'.

Is there any way of getting rid of the "0" in sheet 2 cells B2, B4:B8 in my dummy spreadsheet to just leave a blank cell as in sheet 1 relevent column for I?

[Cutter]

That's what I answered in Post #5 - use:

Please Login or Register  to view this content.

Paste that formula in Sheet 2 B2 and copy down to B8

[Vic78]

Thank you so much for all your help Cutter. I will use the formula as in post 9.

No doubt I will be annoying you in the near future with more queries to solve. Until then, a big THANK YOU!!!!

[Cutter]

And a big YOU'RE WELCOME.

Glad I could help

Don't forget to mark your thread as SOLVED