Microsoft Excel - Cell Reference Question - Asked By Amy Lopata on 10-May-12 04:03 PM

Earn up to 30 extra points for answering this tough 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?

wally eye replied to Amy Lopata on 10-May-12 06:22 PM
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:

Public Function wksIndex() As Integer
  
  If TypeName(Application.Caller) = "Range" Then
    wksIndex = Application.Caller.Parent.Index
  Else
    wksIndex = -1
  End If
  
End Function

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.
Reena Jain replied to Amy Lopata on 11-May-12 03:16 AM
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.
Pichart Y. replied to Amy Lopata on 11-May-12 01:09 PM
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..