IF ISNA VLOOKUP Multi Sheet Help

[steve21nj]

I am taking over a 5 year old payrole record keeper project, where every employee manually enters their work hours. For individual records, each excel file has 28 sheets! A sheet for each pay period, a sheet defining the pay periods, and a summary sheet.

On each pay period sheet, there is a locked field [S1] that has the pay period number.
For example:
sheet1 has the value of 1 on [S1]
sheet2 has the value of 2 on [S1]

On my summary sheet, there is a spot to put the pay period number, (1-26).

What I want to do is the user to manually enter the pay period number on the summary sheet [H13] and it will search each sheet until the values are equal, then pull certain data back.
For example:
user enters 8 (for pay period 8), it searches sheet1-sheet26 until the values of [S1] and [H13] are equal, then returns a value that I am seeking.

The value on the sheet would be [I22] or some other value depending on what the summary sheet was requesting.

Below is a sample of what I am attempting to do.

[FONT=Arial][SIZE=3]=IF(ISNA(VLOOKUP(H13="","",PP1!S1="","",I22)),IF(ISNA(VLOOKUP(H H13="","",PP2!S1="","",I22))[/SIZE][/FONT]

Again [S1] is on the individual pay period sheets, [H13] is on the summary sheet, and [I22] is the value I am requesting from the pay period sheet.

Any thoughts?

 

[Brianwarnock]

I can't say that I understand but surely you know what sheet ie if 8 is entered then you want sheet8, this information can be easily used in code to address the sheet, can't think how to do this in a formula and not able to look at it at the moment.

Brian

 

[steve21nj]

I attached a sample of what I am attempting to do.

There are 4 sheets:
Summary
PP1
PP2
PP3

On the summary I want to enter a value from 1-26 (in this case of my sample it is 1-3) and run my function.

IFERROR(VLOOKUP(H13,PP1!S1),I22,False)),(VLOOKUP(H13,PP2!S1),I22,False)),(VLOOKUP(H13,PP3!S1),I22,False))

I have seen examples of IFERROR(VLOOKUP working, but never to match the value of another sheet before proceeding to take the value.

Any suggestions?

 

[Brianwarnock]

I'm only on 2002 so cannot use your workbook but as I said I would use code.

In the on worksheet change event I would code

Dim n As Long
If Target.Address = "[COLOR="Red"]$H$13[/COLOR]" Then
n = Worksheets("[COLOR="red"]summary[/COLOR]").Range("[COLOR="red"]H13[/COLOR]")
Range("[COLOR="red"]B1[/COLOR]") = Worksheets("pp" & n).Range("[COLOR="red"]I22[/COLOR]")
End If

The red bits need changing to your requirements if I've got them wrong
B1 is where you have typed your formula ie where you want the result.

Brian

 

[Brianwarnock]

Looks like the B1 should be H16 after I opened the Xlxs for reading.

Brian

 

[steve21nj]

Brian,

That is working really well for me. Quick question, since my sample only returned one value and my real working project has many, is there a limit to the line:

Range("H18") = Worksheets("pp" & n).Range("I22")

As of now, I have ran it with returning 14 values with no issue. Would it be an issue for 40 values? Also can I do the Sum of a column with the above code?
Would it look like:

Range("H18") = Worksheets("pp" & n).Range(Sum("I6:I22")

Thank you again!

 

[Brianwarnock]

Firstly I can't see why there should be a limit, but then I've never pushed it.

Should be no limit.

You can do arithmetic in the code but not as you have it Range requires a Range object such an address.
I haven't got access to my pc to test this but if you are summing on the summary sheet say into h17

Range("H17")= sum("A1":"A32")
Would probably work

If from the pp sheet then

Range("H17")= worksheets("pp" & n)!range("a1:a32")

I am assuming that you know the range, is it dynamic , if so how as I'll have to look at it over the weekend as I'm off out shortly.

Brian

 

[Brianwarnock]

I realised that I had missed sum out of my formula and went to edit it but noticed that you have edited yours and it now looks correct. Have you tried it?

Brian

 

[Brianwarnock]

Just about to leave when I realised I had gone nuts the correct code would be

Range("H18") = sum(Worksheets("pp" & n).Range("I6:I22"))


Brian

 

[NBVC]

If the sheets are named correspondingly to the PP # as per your sample, then you could easily use a simple formula in H16:

=INDIRECT("'PP"&H13&"'!I22")

 

[Brianwarnock]

Hmm! Don't know what I did wrong when I tried INDIRECT but I got a syntax error, didn't try very hard as I knew how to do it in code.
Trying again with wht I thought I'd typed it worked but I didn't have the single'

Is it necessary?

BTWif you know the Sum range I think it will be , for example

= INDIRECT("pp"&H13&"!A1:A32"))

Brian

 

[NBVC]

In this specific example, the single quotes are not necessary. This is because the sheetnames in this case do not have spaces or special characters. If a sheetname has space, e.g PP 1, then the single quotes are necessary. It is, however, good practice to always use them in this type of formula, because you never know what the actual sheetnames will be or will be changed to later....

So both:

=INDIRECT("'PP"&H13&"'!I22")

and

=INDIRECT("PP"&H13&"!I22")

will work.

BTW, to SUM a range don't forget the SUM function

=SUM(INDIRECT("'PP"&H13&"'!I22:I25"))

 

[Brianwarnock]

BTW, to SUM a range don't forget the SUM function

=SUM(INDIRECT("'PP"&H13&"'!I22:I25"))

LOL

Than you for the explanation regarding the single '

Brian