Auto populating cells

[hnnn]

I use the following formulas to automatically populating cells for a single record in my sheets:



A='sheet1'!BG6

B='sheet1'!FW6

C='sheet1'!BA6

D='sheet1'!BP6

E='sheet1'!Q6

F='sheet1'!O6

G='sheet1'!L6

H='sheet1'!E6

I='sheet1'!D6

J='sheet1'!A6

K='sheet1'!CI6



Now my problem is that I have got over 200 records.Please help so that this formula is working for all records when selected randomly.



For example if i want a record on number 5 i should not enter its on formula.the above formulas is for a record on number 6.



Please help.

[TMS]

Have you tried dragging the formula up and down? Or just copying and pasting to another row?

The cell reference (row part) should adjust automatically.

Regards

[teylyn]

Hello,

with your desired record number in A1 and assuming that the record number equals the row number:

A=Index('sheet1'!BG:BG,A1)

B=Index('sheet1'!FW:FW,A1)

C=Index('sheet1'!BA:BA,A1)

etc. Do you see the pattern? When you change the number in A1, you will see a different record returned for your cells A, B and C

In case the record number is not equal to the row number, we can set up something different. Just pipe up if you need this.

cheers,

[teylyn]

TM, I think this is probably used in a form-like setup. Enter the record number in one cell, and the form populates with the data.

If it were a table or list setup, of course, you'd be spot on.

cheers,

[TMS]

@Teylyn: thanks, agreed. I just didn't interpret the question that way ... but in view of your reply, I suspect that you are correct. :-)

Regards

[hnnn]

I use the following formulas to automatically populating cells for a single record in my sheets:

A='sheet1'!BG6

B='sheet1'!FW6

C='sheet1'!BA6

D='sheet1'!BP6

E='sheet1'!Q6

F='sheet1'!O6

G='sheet1'!L6

H='sheet1'!E6

I='sheet1'!D6

J='sheet1'!A6

K='sheet1'!CI6

Now my problem is that I have got over 200 records(database).Please help so that this formula is working for all records when selected randomly.Please not that these formulas are on the form with charts with space to be filled in. what i want is visual basic code that will automate this formula for all the records.

For example if i want a record on number 5 i should not enter its on formula.the above formulas is for a record on number 6.



Please help.

[teylyn]

hnnn,

what's the problem?

Have you read any of the comments above?

Why are you repeating your question verbatim?

And can you please update your profile with your actual country? I know it's not England. That bit of information helps us to figure out what your regional settings are (which is quite important in some aspects of Excel) and whether or not English might be your first language. This is information that helps us help you, so don't try to cheat.

[TMS]

@hnnn: you've had two different approaches offered which reflect alternative interpretations of your scenario.

It would help a lot if you were to post a sample workbook so that we can see if either of the options is appropriate, if one of them can be adapted, or if we need to start from scratch.

Regards