Pulling data daily from a differently named report everyday

Hello,

I have a spreadsheet where I use formulas to pull data from multiple
reports on a shared server everyday. What I do is to save those
reports under a certain name everyday since the formulas have to have
the static report name in them to pull data. (For example, A1 may pull
data from c:\reportfolder\reportA, B1 may pull from c:
\reportfolder2\reportB, etc). In these report folders our IT group
runs a new report in it everyday but of course they change the name of
the report every day (to reflect the date) . . so in the reportfolder
there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
"reportA-3-14-2009, etc). What I would like is to have a formula that
pulls it's data from the most recent one. Is it possible?

Thank you :)
0
8/7/2009 8:57:41 PM
excel 39879 articles. 2 followers. Follow

9 Replies
1019 Views

Similar Articles

[PageSpeed] 21

The function you'd want to use that's built into excel is =indirect().  But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

ACG wrote:
> 
> Hello,
> 
> I have a spreadsheet where I use formulas to pull data from multiple
> reports on a shared server everyday. What I do is to save those
> reports under a certain name everyday since the formulas have to have
> the static report name in them to pull data. (For example, A1 may pull
> data from c:\reportfolder\reportA, B1 may pull from c:
> \reportfolder2\reportB, etc). In these report folders our IT group
> runs a new report in it everyday but of course they change the name of
> the report every day (to reflect the date) . . so in the reportfolder
> there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
> "reportA-3-14-2009, etc). What I would like is to have a formula that
> pulls it's data from the most recent one. Is it possible?
> 
> Thank you :)

-- 

Dave Peterson
0
petersod (12004)
8/7/2009 9:37:02 PM
On Aug 7, 4:37=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> The function you'd want to use that's built into excel is =3Dindirect(). =
=A0But that
> function returns an error if the sending workbook is closed.
>
> If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:h=
ttp://xcell05.free.fr/
> orhttp://xcell05.free.fr/morefunc/english/index.htm
>
> That includes =3Dindirect.ext() that may help you.
>
>
>
>
>
> ACG wrote:
>
> > Hello,
>
> > I have a spreadsheet where I use formulas to pulldatafrom multiple
> > reports on a shared server everyday. What I do is to save those
> > reports under a certain name everyday since the formulas have to have
> > the static report name in them to pulldata. (For example, A1 may pull
> >datafrom c:\reportfolder\reportA, B1 may pull from c:
> > \reportfolder2\reportB, etc). In these report folders our IT group
> > runs a new report in it everyday but of course they change the name of
> > the report every day (to reflect the date) . . so in the reportfolder
> > there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
> > "reportA-3-14-2009, etc). What I would like is to have a formula that
> > pulls it'sdatafrom the most recent one. Is it possible?
>
> > Thank you :)
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

The download doesn't seem to work on those pages. Any other ideas on
how to accomplish it?
0
8/10/2009 1:05:54 PM
Try searching Google for morefunc.xll.

But depending on the site you download from, you may not be getting the most
current version.  I'd visit that site every couple of days looking to see if
it's ok.

ACG wrote:
> 
> On Aug 7, 4:37 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > The function you'd want to use that's built into excel is =indirect().  But that
> > function returns an error if the sending workbook is closed.
> >
> > If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:http://xcell05.free.fr/
> > orhttp://xcell05.free.fr/morefunc/english/index.htm
> >
> > That includes =indirect.ext() that may help you.
> >
> >
> >
> >
> >
> > ACG wrote:
> >
> > > Hello,
> >
> > > I have a spreadsheet where I use formulas to pulldatafrom multiple
> > > reports on a shared server everyday. What I do is to save those
> > > reports under a certain name everyday since the formulas have to have
> > > the static report name in them to pulldata. (For example, A1 may pull
> > >datafrom c:\reportfolder\reportA, B1 may pull from c:
> > > \reportfolder2\reportB, etc). In these report folders our IT group
> > > runs a new report in it everyday but of course they change the name of
> > > the report every day (to reflect the date) . . so in the reportfolder
> > > there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
> > > "reportA-3-14-2009, etc). What I would like is to have a formula that
> > > pulls it'sdatafrom the most recent one. Is it possible?
> >
> > > Thank you :)
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
> 
> The download doesn't seem to work on those pages. Any other ideas on
> how to accomplish it?

-- 

Dave Peterson
0
petersod (12004)
8/10/2009 3:36:11 PM
On Aug 10, 10:36=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Try searching Google for morefunc.xll.
>
> But depending on the site you download from, you may not be getting the m=
ost
> current version. =A0I'd visit that site every couple of days looking to s=
ee if
> it's ok.
>
>
>
>
>
> ACG wrote:
>
> > On Aug 7, 4:37 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > The function you'd want to use that's built into excel is =3Dindirect=
(). =A0But that
> > > function returns an error if the sending workbook is closed.
>
> > > If that's a problem, then Laurent Longre has an addin (morefunc.xll) =
at:http://xcell05.free.fr/
> > > orhttp://xcell05.free.fr/morefunc/english/index.htm
>
> > > That includes =3Dindirect.ext() that may help you.
>
> > > ACG wrote:
>
> > > > Hello,
>
> > > > I have a spreadsheet where I use formulas to pulldatafrom multiple
> > > > reports on a shared server everyday. What I do is to save those
> > > > reports under a certain name everyday since the formulas have to ha=
ve
> > > > the static report name in them to pulldata. (For example, A1 may pu=
ll
> > > >datafrom c:\reportfolder\reportA, B1 may pull from c:
> > > > \reportfolder2\reportB, etc). In these report folders our IT group
> > > > runs a new report in it everyday but of course they change the name=
 of
> > > > the report every day (to reflect the date) . . so in the reportfold=
er
> > > > there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
> > > > "reportA-3-14-2009, etc). What I would like is to have a formula th=
at
> > > > pulls it'sdatafrom the most recent one. Is it possible?
>
> > > > Thank you :)
>
> > > --
>
> > > Dave Peterson- Hide quoted text -
>
> > > - Show quoted text -
>
> > The download doesn't seem to work on those pages. Any other ideas on
> > how to accomplish it?
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

I've been testing the INDIRECT function just to se how it might work
once I got the add-in going and an issue that has come up is that, the
last name of the filename (the part that's changing daily) is the
date . . .added to the end of everyfilename in the format 2009-2-11
(for feb 11 2009). When I'm try to have INDIRECT add the different
parts of the filename from each of the cells I've typed them in, it
always brings in the date (for which I've just used NOW()-1 (to get
yesterday)) in the format of 432354354.1224343 instead of 2009-2-11,
even if I've formatted the cells with yyyy-mm-dd. Wont seem to change
no matter what I try.
0
8/10/2009 3:56:33 PM
=now() includes the date and time.
=Today() is just the date.

....&text(today()-1,"m-d-yyyy")&...

(modify the formatting string to what you need (mm and dd are two digit months
and years).)

(ps.  I'm not sure what order the format is in, either.  You seemed to change
from the original post.)

ACG wrote:
> 
> On Aug 10, 10:36 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > Try searching Google for morefunc.xll.
> >
> > But depending on the site you download from, you may not be getting the most
> > current version.  I'd visit that site every couple of days looking to see if
> > it's ok.
> >
> >
> >
> >
> >
> > ACG wrote:
> >
> > > On Aug 7, 4:37 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > The function you'd want to use that's built into excel is =indirect().  But that
> > > > function returns an error if the sending workbook is closed.
> >
> > > > If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:http://xcell05.free.fr/
> > > > orhttp://xcell05.free.fr/morefunc/english/index.htm
> >
> > > > That includes =indirect.ext() that may help you.
> >
> > > > ACG wrote:
> >
> > > > > Hello,
> >
> > > > > I have a spreadsheet where I use formulas to pulldatafrom multiple
> > > > > reports on a shared server everyday. What I do is to save those
> > > > > reports under a certain name everyday since the formulas have to have
> > > > > the static report name in them to pulldata. (For example, A1 may pull
> > > > >datafrom c:\reportfolder\reportA, B1 may pull from c:
> > > > > \reportfolder2\reportB, etc). In these report folders our IT group
> > > > > runs a new report in it everyday but of course they change the name of
> > > > > the report every day (to reflect the date) . . so in the reportfolder
> > > > > there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
> > > > > "reportA-3-14-2009, etc). What I would like is to have a formula that
> > > > > pulls it'sdatafrom the most recent one. Is it possible?
> >
> > > > > Thank you :)
> >
> > > > --
> >
> > > > Dave Peterson- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > The download doesn't seem to work on those pages. Any other ideas on
> > > how to accomplish it?
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
> 
> I've been testing the INDIRECT function just to se how it might work
> once I got the add-in going and an issue that has come up is that, the
> last name of the filename (the part that's changing daily) is the
> date . . .added to the end of everyfilename in the format 2009-2-11
> (for feb 11 2009). When I'm try to have INDIRECT add the different
> parts of the filename from each of the cells I've typed them in, it
> always brings in the date (for which I've just used NOW()-1 (to get
> yesterday)) in the format of 432354354.1224343 instead of 2009-2-11,
> even if I've formatted the cells with yyyy-mm-dd. Wont seem to change
> no matter what I try.

-- 

Dave Peterson
0
petersod (12004)
8/10/2009 6:44:27 PM
On Aug 10, 1:44=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> =3Dnow() includes the date and time.
> =3DToday() is just the date.
>
> ...&text(today()-1,"m-d-yyyy")&...
>
> (modify the formatting string to what you need (mm and dd are two digit m=
onths
> and years).)
>
> (ps. =A0I'm not sure what order the format is in, either. =A0You seemed t=
o change
> from the original post.)
>
>
>
>
>
> ACG wrote:
>
> > On Aug 10, 10:36 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > Try searching Google for morefunc.xll.
>
> > > But depending on the site you download from, you may not be getting t=
he most
> > > current version. =A0I'd visit that site every couple of days looking =
to see if
> > > it's ok.
>
> > > ACG wrote:
>
> > > > On Aug 7, 4:37 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > > The function you'd want to use that's built into excel is =3Dindi=
rect(). =A0But that
> > > > > function returns an error if the sending workbook is closed.
>
> > > > > If that's a problem, then Laurent Longre has an addin (morefunc.x=
ll) at:http://xcell05.free.fr/
> > > > > orhttp://xcell05.free.fr/morefunc/english/index.htm
>
> > > > > That includes =3Dindirect.ext() that may help you.
>
> > > > > ACG wrote:
>
> > > > > > Hello,
>
> > > > > > I have a spreadsheet where I use formulas to pulldatafrom multi=
ple
> > > > > > reports on a shared server everyday. What I do is to save those
> > > > > > reports under a certain name everyday since the formulas have t=
o have
> > > > > > the static report name in them to pulldata. (For example, A1 ma=
y pull
> > > > > >datafrom c:\reportfolder\reportA, B1 may pull from c:
> > > > > > \reportfolder2\reportB, etc). In these report folders our IT gr=
oup
> > > > > > runs a new report in it everyday but of course they change the =
name of
> > > > > > the report every day (to reflect the date) . . so in the report=
folder
> > > > > > there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
> > > > > > "reportA-3-14-2009, etc). What I would like is to have a formul=
a that
> > > > > > pulls it'sdatafrom the most recent one. Is it possible?
>
> > > > > > Thank you :)
>
> > > > > --
>
> > > > > Dave Peterson- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > The download doesn't seem to work on those pages. Any other ideas o=
n
> > > > how to accomplish it?
>
> > > --
>
> > > Dave Peterson- Hide quoted text -
>
> > > - Show quoted text -
>
> > I've been testing the INDIRECT function just to se how it might work
> > once I got the add-in going and an issue that has come up is that, the
> > last name of the filename (the part that's changingdaily) is the
> > date . . .added to the end of everyfilename in the format 2009-2-11
> > (for feb 11 2009). When I'm try to have INDIRECT add the different
> > parts of the filename from each of the cells I've typed them in, it
> > always brings in the date (for which I've just used NOW()-1 (to get
> > yesterday)) in the format of 432354354.1224343 instead of 2009-2-11,
> > even if I've formatted the cells with yyyy-mm-dd. Wont seem to change
> > no matter what I try.
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Dave,

Thank you very much for  your time and answers. It is working just
like you said. Unfortunately Morefunc keeps craching Excel and even if
I'm lucky enough for it not to, these formulas take so long to pull
the data it would be more effective to do it manually. I do sincerely
appreciate your time and knowledge :) Cheers
0
8/11/2009 5:31:33 PM
ACG <agrandst...@hotmail.com> wrote...
>I have a spreadsheet where I use formulas to pull data from multiple
>reports on a shared server everyday. What I do is to save those
>reports under a certain name everyday since the formulas have to have
>the static report name in them to pull data. (For example, A1 may pull
>data from c:\reportfolder\reportA, B1 may pull from c:
>\reportfolder2\reportB, etc). In these report folders our IT group
>runs a new report in it everyday but of course they change the name of
>the report every day (to reflect the date) . . so in the reportfolder
>there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
>"reportA-3-14-2009, etc). What I would like is to have a formula that
>pulls it's data from the most recent one. Is it possible?

I've had to deal with this over the years. The simplest way I've found
is copying the current day's file to a different location but with a
fixed filename, then have my formulas refer to the copied file using
the invariant filename. The file copies can be handled either by VBA
macros, plain old batch files or even manually.
0
hrlngrv1 (375)
8/11/2009 6:07:32 PM
And just add to Harlan's thoughts, copy the file to a local drive (like your
harddrive).

It should work faster from there than from a network drive (even if that is
local).

And if you see a slowdown, open that file first.

Harlan Grove wrote:
> 
> ACG <agrandst...@hotmail.com> wrote...
> >I have a spreadsheet where I use formulas to pull data from multiple
> >reports on a shared server everyday. What I do is to save those
> >reports under a certain name everyday since the formulas have to have
> >the static report name in them to pull data. (For example, A1 may pull
> >data from c:\reportfolder\reportA, B1 may pull from c:
> >\reportfolder2\reportB, etc). In these report folders our IT group
> >runs a new report in it everyday but of course they change the name of
> >the report every day (to reflect the date) . . so in the reportfolder
> >there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
> >"reportA-3-14-2009, etc). What I would like is to have a formula that
> >pulls it's data from the most recent one. Is it possible?
> 
> I've had to deal with this over the years. The simplest way I've found
> is copying the current day's file to a different location but with a
> fixed filename, then have my formulas refer to the copied file using
> the invariant filename. The file copies can be handled either by VBA
> macros, plain old batch files or even manually.

-- 

Dave Peterson
0
petersod (12004)
8/11/2009 6:38:57 PM
On Aug 11, 1:38=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> And just add to Harlan's thoughts, copy the file to a local drive (like y=
our
> harddrive).
>
> It should work faster from there than from a network drive (even if that =
is
> local).
>
> And if you see a slowdown, open that file first.
>
>
>
>
>
> Harlan Grove wrote:
>
> > ACG <agrandst...@hotmail.com> wrote...
> > >I have a spreadsheet where I use formulas to pulldatafrom multiple
> > >reports on a shared server everyday. What I do is to save those
> > >reports under a certain name everyday since the formulas have to have
> > >the static report name in them to pulldata. (For example, A1 may pull
> > >datafrom c:\reportfolder\reportA, B1 may pull from c:
> > >\reportfolder2\reportB, etc). In these report folders our IT group
> > >runs a new report in it everyday but of course they change the name of
> > >the report every day (to reflect the date) . . so in the reportfolder
> > >there will be "reportA-3-12-2009", and "reportA-3-13-2009", and
> > >"reportA-3-14-2009, etc). What I would like is to have a formula that
> > >pulls it'sdatafrom the most recent one. Is it possible?
>
> > I've had to deal with this over the years. The simplest way I've found
> > is copying the current day's file to a different location but with a
> > fixed filename, then have my formulas refer to the copied file using
> > the invariant filename. The file copies can be handled either by VBA
> > macros, plain old batch files or even manually.
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Yeah, that's what I've been doing. Was just hoping to find a way where
I didn't have to do anything and the report would still update
everytime it was opened. BUt I can live doing it this way. Thank you
both very much!
0
8/12/2009 12:54:56 PM
Reply:

Similar Artilces:

How do I create a list when it is not in the Data tool bar?
I need to create a list, but when I drop down the Data from the tool bar, "list" is not an option under it. What should I do? Hi Jamie, Until the most recent XL version, a list was just an informal way of organizing data in a worksheet. It still is, really. It's just a matter of putting different records in different rows, different variables (or "fields") in different columns, and the name of each variable in the list's first row. The column variables should be adjacent to one another, as should the records in the rows. Some Excel features work much more easily...

Excel>Data>Import External Data>New Web query #2
Hi I am trying to use Excel 2003 to import data from http://bigcharts.marketwatch.com/quotes/default.asp?refresh=on&rand=8519 I use new web query on the above url. I select the data in the block under 'My Favourite Quotes' The wizard behaves as expected but only returns 1 line not the 3 that have been selected. Can someone please give me some pointers on how I can get the 3 lines. Thanks Bruce Try yahoo instead or go here and get my FREE excel file for quotes or history. Look for donaldb36 as author. xltraders@yahoogroups.com -- Don Guillett SalesAid Software donaldb@...

Repost: Test Linked form for data
This was originally posted on September 14, 2007. "Test Linked form for data" Hi Groupies How can I test to see if a linked form, that might not be visible, has data? My main form has a yes/no box that enables a button when it is set to yes. The button opens a linked form. When the user moves to the next record, I would like the main forms BeforeUpdate event to make sure that the yes/no box is not set at No while there is data in the linked form for that record. I have tried something like: If Me.Form.frmVehicles.RecordsetClone.RecordCount > 0 And Me.CompanyVehicle = False ...

Troubleshoot Publisher Data Source
Everytime I try to Mail Merge my data source (Excel file) I get a "dialog or data base engine failure" How do I correct this so I can use the data in my publication? Refresh your MDAC components http://msdn.microsoft.com/data/ Click on MDAC 2.8 on the right pane -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "NancyF" <NancyF@discussions.microsoft.com> wrote in message news:6E0A079E-E6C4-4710-B07F-B6F0D523BBC3@microsoft.com... > Everytime I try to Mail Merge my data source (Excel file) I get a "dia...

Printing a Report with Parameters
When printing a report with Parameters. The report will come up on the screen after you enter the parameter. When you then print the report it asks for the parameter again. After we click finish, the following error appears on the screen 'Information is needed before this report can be processed (On Cache Page/Server EIACRM.pgsvr) The report will not print. what server os is your crm web running and also what is your domain controller(s) running? also its possible you have embedded the servername in the report. open the report in crystal designer and go to database-verify dat...

X,Z,ZZ REPORT
Hi, ANy way to get a shorter form of x,z,zz report for RMS? Does Anyone know how to customise the z report? Regards kase Hi Mickie, I don't see any attachement as mentioned, where can I get one ? may be you can email me the report to kase4c@yahoo.com Thanks. kase "Mickie" wrote: > kase wrote: > > Hi, > > ANy way to get a shorter form of x,z,zz report for RMS? > > Does Anyone know how to customise the z report? > > > > Regards > > kase > > I attached my SHORT report. I only having IN/OUT and over/short. I > deleted all the ...

using Access Data
I have a slight problem with Charts in Excel & Access. I'm creating charts that can use up to 50,000 data points (if not more) and in Excel there is significant lag time (up to 2 minutes just to rescale an axis or change the title). I decided to try and use Access to organize the data, but creating a chart in Access is a pain because there is a 4000 data point limit per series (really stupid). My question is: Is there a way to link the data from Access to an Excel chart without importing the data (importing would just cause my initial problem)? I do know some basic...

Lost Data #3
I attempted to access my Outlook 2003 this morning and was unable to. It asked for a new profile. It appears that I've lost all my addresses, contacts, emails, and calendar/appointment set-ups. Help please, how do I get this back?????? Crying in the islands. "oahumannings" <oahumannings@hotmail.com(donotspam)> wrote in message news:820CFF8E-B741-4CBF-B4F1-45EF7E79BD24@microsoft.com... > I attempted to access my Outlook 2003 this morning and was unable to. It > asked for a new profile. It appears that I've lost all my addresses, > contacts, emails, and...

Data Validation in Excel 2002
I have a workbook with a sheet in called 'Validation'. This sheet is hidden and contains a number of lists that I use for data validation. Using previous versions on Excel, I have always defined names for these lists then on other sheets within the workbook I use the data validation command using the List option in the Allow field then enter the name of the list in the Source field, using the option for an In-cell Dropdown. Upon entering that cell a drop down list was available populated with the list from my Validation sheet. Now in Excel 2003, when I attempt the same and look at t...

Data migration 03-06-07
I'm in the process of migrating data from a 3:rd party system into MS CRM 3.0. But when I look at the staging database I can't figure out what to do with the appointments. Although there is an Appointment table in the CRM database, it seems to be missing from the cdf database. Does anyone knows in what table the appointments should go? Also, does anyone knows where to find any detailed documentation about Data Migration Framework? I've been searching but I haven't been able to find any good documentation. Thanks /Peter Hi Peter, You are unable to import appointments w...

Off Topic: Pulling Hair Out
Want to have some fun? Search microsoft.public.pos on google groups for "hair." You'll get all of the posts about people pulling thier hair out. Hint: There's more than one page. ...

Data Validation #10
Hello, I am using Excel 2003 and am doing a drop down list using Data|Validation. However, I must have the data for my drop down list in the same sheet as my drop down list, otherwise the drop down lsit doesn't work. Am I doing something wrong? I used to be able to put the data in a different sheet. What happened? Valmont Valmont It's OK to have the list in another sheet, but then you must name it, e.g. "List1" (without quotes), and use that name in the validation box (Allow: List and enter =List1) -- Best Regards Leo Heuser Followup to newsgroup only please. &quo...

how i get data homogeneity
there is a formula for that i don't know it What is the degree of homogeneity you are trying to acheive? -- Gary''s Student - gsnu200838 "new user" wrote: > there is a formula for that i don't know it See http://tinyurl.com/ccrec5 "new user" wrote: > there is a formula for that i don't know it i have a data and i want know the hohmogeneity for it ; 95 % of the normal distribution near the optimum point of its chart Maybe checking a statistics book to find the methodology is step 1. Then we can help you execute this in Excel best w...

Pull down menu
Hello, I am trying find a way to make a pull down menu in excel. I have a list of foods on one sheet in a workbook where all the nutrition information is stored per food type where the row indicates the food type and the columns indicate the nutrition info ie: Carbs, Protein, Fats, Calories etc. This sheet has roughly 300 or so food items listed in rows. How can I create a pull down menu in another cell on another worksheet as a method to select a food item from the food sheet? Any help is appreciated, Dave --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (...

Can you control when the data is saved?
I created a form that allowes add and update. There are several combo box controls. After a number of the records were added, users bring up the form and for some reason fiddle/play with the combo box controls. This results in the data being changed. And they don't seem to realize this is happening. Is there a way to have Access avoid changing the record unless the user overtly selects save (or say presses a button to save)? Thanks. On Wed, 5 Dec 2007 19:20:02 -0800, JHC <JHC@discussions.microsoft.com> wrote: Yes. One way is a trusted one, with very little recidivis...

Dividend & Commissions REPORT
Please advise how a report can be generated evidencing all recorded dividend payments & recorded commission payments. I am currently utilising Money 2000 ...

How many data points can you have in an Excel for a Scatter Chart.
I have a customer that has indicated that he can only plot 400 data bites in an Excel Scatter Chart. Is the correct for Excel 2002 or 2003? What does a "data bite" represent? What error message do you get? You should check the XL help topic 'Excel specifications and limits' specifically the 'Charting specifications' section. One undocumented limit is that the length of any token in the SERIES formula cannot exceed be more than about 250 characters. So, if one selects lots of non-contiguous ranges, it is very possible to exceed that limit. -- Regards, Tus...

2007
Hi! - I was hoping someone could help me please. Here's what I have. A worksheet that has several dates in a row. D1:T1, Of that range, I have conditional formatting set to highlight a date(s) either = to or within the next 7 days. I need to be able to pull the date next closest or equal to today. Can anyone help me out? Any help would be greatly appriecated. Shaun > I was hoping someone could help me please. Here's what I have. A > worksheet that has several dates in a row. D1:T1, Of that range, I > have conditional formatting set to highlight a date(s) either = to or...

Excel 03 DB query pulls data in wrong order, how to resolve?
New user to Excel DB queries. Have set up DB and query, however query does not import DB in the column-order of the query. This worked fine the past couple months. Dont understand why it wont work this month. Have re-created query and the problem still exists. Cant find any information on how to troubleshoot this issue. Thanks for your help. Data>Import External Data>Data Range Properties Uncheck Preserve column sort/filter/layout Does that help? -- Regards, Ron ...

External data limitation
I apologize for the duplicate post! We are having trouble downloading 80 fields from a DateTel database into Excel, and consistently get errors until we drop the fields down to around 60. Is there a limitation to the number of fields that may be imported into Excel?? Sandy -- IT Services Help Center Yes there is but it is way more than 80 .... Unless you are using a trial copy? -- John C. Harris, MPA JCZ Consulting Services, LLC "Sandy" <Sandy@discussions.microsoft.com> wrote in message news:34477098-6835-4138-A706-A5FB77BBAD63@microsoft.com... >I apologize for th...

Limit data synchronization in Outlook
Hi, I try to find a solution to limit all user to synchronize his own record for security reason in Outlook. I know CRM have a checkbox for BU only but this check box can be remove by the user, theire is not a big security... Did somebody know ISV or something else for that? Thanks, Sylvie ...

Data into table
Hi all When I enter date stright into table, I cant find the record with a query, only if I enetr the record with the related form. I paid extra attention no to have any mistakes in trms of spelling etc.. What can cause this? Thanks, Tom you've probably just got the wrong where clause man :) "Tom" <partner1973@yahoo.com> wrote in message news:uejJS4inHHA.3968@TK2MSFTNGP06.phx.gbl... > Hi all > > When I enter date stright into table, I cant find the record with a query, > only if I enetr the record with the related form. > > I paid extra a...

Pivot Table-Change Data Source? Excel 2007
I need to update a series of pivot tables by adding 2010 data. What's the best way to do so? Add the updated info to the source document and refresh, or create a new data source and refresh all the tables? Does the new source have to have the columns in the same order as the previous? If I create a new source document, how do I tell the tables to change the source? Other responses to this question say to rightclick a cell and use the wizard. I'm using Excel2007 and right clicking doesn't produce a wizard. Thanks To change the date source of a PivotTable in Excel...

Report Grand Total Miscalculating Overtime
I have a report based on a query that uses the following calculation in a text box to calculate the total amount paid (including overtime) to an employee: =([On Std Hours]+[Off Std Hours] + IIF([On Std Hours]+[Off Std Hours]>40, .5* ([On Std Hours]+[Off Std Hours]-40), 0)) * [Employee Data_Rate] This formula works perfectly to total individual weekly totals. However, whenever I use the same calculation in the report footer to calculate the grand total which sums up every employee for the week, the grand total is a lot higher than it should be. For example, I hand computed the to...

Import External Data is missing some data
I have a spreadsheet that pulls data from another spreadsheet. Most of the data come in fine. However, one collumn is being difficult. The collumn in question contains dates and comes over blank. If I put alpha data in the source spreadsheet, though, that WILL import. Any idea what I'm doing wrong? Thanks in advance for any assistance! ...