Macro to autopopulate references

**ab11viva** · 10-22-2009, 11:14 AM

Hello,

This is my first post to this forum and I am definitely a novice when it comes to macros.

I have a spreadsheet (attached) that I would like to function a little smarter. Basically, there are 3 columns (Name, Reference to PQS, Reference to GPB) that represent procedure numbers and any references to other procedure numbers that are contained in Column A. (column A contains a list of all procedure numbers of both types - PQS and GPB)

What I would like to do is have the spreadsheet automatically update the references when a reference is created. For example, the second row shows C1000 (A2) referencing Q1221 (B2). I would then like the spreadsheet to update the Q1221 row (297) with the value of C1000 in column C. Basically, this is a cross reference. (I want the values to match for all values of A)

I hope this makes a little sense - please let me know if I need to clarify anything.

**StephenR** · 10-22-2009, 11:26 AM

So for every value in col A, if there is a value in col B you want a corresponding entry in col C, and if there is a value in col C you want a corresponding entry in col B - is that right?

Are you asking for this for your existing database and henceforth, or just henceforth "when a reference is created"? If the former, then I reckon a two-stage process would be required.

**ab11viva** · 10-22-2009, 11:51 AM

Stephen,

Thanks for your quick reply.

The values in column A are actually 2 different procedure types(PQS and GPB). These can reference other procedures of either type and this is reflected in column B and C. In my actual spreadsheet, there is a column that indicates what type of procedure this is.

It is not necessary that for every value A, there is a B and a C but rather we want the B and C values to be consistent with the corresponding A. A small example table is attached.

Not sure if that helps but basically if col A references a value in either B or C, I want the B or C values (which would also be in column A) to reference the original A value.