Auto populating and assigning numeric value to a number

[jem972]

Hi
I have a spreadsheet I have been strugling with so I decided to go to
some experts to help me out.
I record vacation, sick, and edo time on an excel spreadsheet. I have
set it up so that the EDO autopopulate because they reoccur once every
4 weeks. Now I am trying to autopopulate the vacation, which is
random.
They submit their vacation in advance so I want to put it into a
yearly calendar and when I update the weekly excel sheet not only
their EDO autopopulate their Vacation does too.
RIght now I have 2 tabs, one for the week that I manually enter their
sick, and vacation, and their EDO (autopopulate). 2nd tab is their
EDO sheet where all their EDO info draws from. I have inserted a 3rd
sheet with a yearly calendar to enter their vacation on but now I want
the 1st sheet to check the EDO sheet and the Vacation sheet and put it
the info into the 1st tab.

Once I have this completed on a weekly basis I also want to sum all
the V (vacation), S (sick), Edo (earned day off) etc. I am trying to
add a number value to them and am not have any luck I I have tried
this formula =SUMPRODUCT(LOOKUP(E3:K3, $O$4:$O$7, $P$4:$P$7))
but am getting an #N/A. My Spreadsheet is kind of complicated and
has
a bunch of different formulas so I am not sure if that may be the
reason I am getting the error or not.

Please Help!!

 

[Sheeloo]

As far as I know, you can not pass an array as the first parameter of the
LOOKUP formula...

 

[~L]

There are quite a few questions contained in this. I'm going to focus on
your provided formula. It is very difficult to design a document remotely,
if you could be specific on your questions and space them out a bit, it would
help people help you.

=SUMPRODUCT(LOOKUP(E3:K3, $O$4:$O$7, $P$4:$P$7))

NA would occur if any of E3:K3 is less than the smallest value of O4:O7, or
if there was no match on any one of E3:K3.

Is that the case?

 

[jem972]

There are quite a few questions contained in this.  I'm going to focus on
your provided formula.  It is very difficult to design a document remotely,
if you could be specific on your questions and space them out a bit, it would
help people help you.

=SUMPRODUCT(LOOKUP(E3:K3, $O$4:$O$7, $P$4:$P$7))

NA would occur if any of E3:K3 is less than the smallest value of O4:O7, or
if there was no match on any one of E3:K3.

Is that the case?







- Show quoted text -

Ok first things first, I want to autopopulate vacation dates from the
3rd spreadsheet into the 1st.
Right now I have EDO autopopulating into the 1st spreadsheet but these
are every 4 weeks where vacation is random.
I could forward my test spreadsheet to you if you wanted? I dont know
how involved in this you want to get, LOL.

 

[~L]

This could be as simple as = Sheet3!A1 or if you have a table of data on one
sheet and a list with some elements from that table on the first sheet, you
may be looking at a VLOOKUP or INDEX. For example, if you have a list of
employees and their vacation dates on sheet 3 and a list of employees with
some other data on sheet 1, a VLOOKUP might go something like:

=VLOOKUP(A2,Sheet3!$A$1:$A$500,2,FALSE)

If you have an upload site and this is not confidential information, go
ahead and post the link.

 

[jem972]

This could be as simple as = Sheet3!A1 or if you have a table of data on one
sheet and a list with some elements from that table on the first sheet, you
may be looking at a VLOOKUP or INDEX.  For example, if you have a list of
employees and their vacation dates on sheet 3 and a list of employees with
some other data on sheet 1, a VLOOKUP might go something like:

=VLOOKUP(A2,Sheet3!$A$1:$A$500,2,FALSE)

If you have an upload site and this is not confidential information, go
ahead and post the link.





- Show quoted text -

I unfortunately dont have a website. If you wanted I could email
you?? the one problem I have is that there are formulas in the first
spreadsheet already so I cant really put another one in, I dont
think. I have worked on it for too long and now my brain hurts when I
try to work on it
Thanks for your help so far, I am completely lost at this point so it
helps to see your ideas.

 

[Gord Dibben]

Upload your file to http://www.savefile.com/

Post the URL that you will be given after the upload.

Anyone who chooses can download the file to assist you.


Gord Dibben MS Excel MVP

 

[jem972]

Upload your file tohttp://www.savefile.com/

Post the URL that you will be given after the upload.

Anyone who chooses can download the file to assist you.

Gord Dibben  MS Excel MVP





- Show quoted text -

http://www.savefile.com/files/1866788

here is the URL. Thanks!