Circular Reference!!

[psbailey42]

Hay guys,

Currently i'm working on a spreadsheet and have desided to create circular references to make it easier to expand said spreadsheet if needs be, however, the circular reference formula will only work for the new part of my spreadsheet and won't allow it to be applied to the existing data, have it broken excel? EXAMPLE:

Old Formula
=COUNTIFS(Sheet1!G:G,"Grant",Sheet1!J:J,"15/12/2012")

New Formula
=COUNTIFS(Sheet1!G:G,B1,Sheet1!J:J,A1)

[Dave H9]

I can't see how this would create a circular reference, even if A1 and C1 are derived from column G or J.

How are you adding your new data? It may be useful to post an example worksheet which produces the problem you are experiencing.

Dave H

[psbailey42]

Hay Dave!

I should of said the information is across to sheets, G refers to the agents name and J refers to the date in sheet 1, the formula i'm using is on sheet 2 and cross references the name and date to hopefully save time in inputting the data.

[Dave H9]

Yes I tried to test it to create a circular reference but connot (see attached).

My previous post still stands, if you could provide an example with the problem you describe we may be able to sort it out.

Cheers

Dave H

[psbailey42]

I think i've cracked it dave! i'll send over my example just so you can see what i've done!

[Dave H9]

Hi psbailey,

Thanks for the feedback, so what was the problem??

Dave H

[psbailey42]

It was something as silly as having both the surname and first name inputted into the columm! If you refer back to my example and remove Mark's surname from sheet 1 the formula doesn't find him! thanks for the help Dave, have a great weekend.

[Dave H9]

Ok mate, didn't really do much. You have a good weekend too.

Dave H