Microsoft Excel - Cell Reference Question

I have a Workbook that contains multiple worksheets. Each new worksheet has the same basic information copied from the first worksheet. Is there a way to reference cells for the first worksheet to the new worksheet? I understand the =Active!$H23 formula, however, each new worksheet needs to reference the next cell, meaning, the next worksheet should read =Active!$H24. The columns will not change, just the row. Is there a way to do this?

You could go through, create a list of worksheets and use this formula to derive an index of those worksheet, then use that index to offset:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,50)

used with a named range containing your worksheet list, it would look like:

=MATCH(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,50),WorksheetList,0)

A bit simpler version would be to create a UDF to return the index.  If you go to the VBA IDE (alt-F11), insert a new module, and paste in this code:


you can just set up your formulas like:

=Offset(Active!$H22,wksindex(),0)

which would look up the worksheet index and offset from Active!H22 down one row.  This does assume your worksheets are in order, and you might have to adjust the range or add or subtract from wksIndex() to get the right reference.

hi,

I think vlookup do the job.
vlookup used for vertical lookup. its search for a value in the first column of a table array and returns a value in the same row from another column in the table array. Try the below formulas with a similar data and try changing the name in Cell C1

ColA	ColB	ColC	ColD	ColE	ColF
Name	Age	Sex	Adam	=formula1	=formula2
Adam	32	M
Marie	24	F
Francis	33	M
Joel	34	M
Susan	26	F

in place of formula1
=VLOOKUP($D$1,$A$2:$C$10,2,FALSE)

in place of formula2
=VLOOKUP($D$1,$A$2:$C$10,3,FALSE)

Now try changing the value in D1..The VLOOKUP() formula will retrieve the age and sex details for the corresponding name in cell D1.

HLOOKUP() is similar to VLOOKUP() except that is horizontal lookup. Try the below example..The table info is exactly same as above except that it is transposed...

ColA	ColB	ColC	ColD	ColE
Adam	Marie	Francis	Joel	Susan
32	24	33	34	26
M	F	M	M	F

With the above table enter a name in cell F1

In cell G1 enter the below formula
=HLOOKUP(F1,A1:E3,2,0)

In H1 enter the below formula
=HLOOKUP(F1,A1:E3,3,0)

Now change the name in F1.

Hi Amy,

I have some idea now, but not sure if it is what you want.

Could you please attach your file here, it help us understand you better, then you get quick and exact answer?

Pichart Y

To attach the file...see below..