spreadsheet

  • Thread starter Thread starter lee
  • Start date Start date
L

lee

i have multiple spreadsheet files and tabs in excel used for
forecasting. Can we use access and conver the files and tabs to
access. the files in excel use a lot of hlook up and v look up and
sumif functions
 
i have multiple spreadsheet files and tabs in excel used for
forecasting. Can we use access and conver the files and tabs to
access. the files in excel use a lot of hlook up and v look up and
sumif functions
Click to expand...

Short answer: Yes.

Real answer: Access is NOT "Excel on Steroids". It's not a spreadsheet; it can
certainly work with your data, and probably do everything that you're doing
now. But it will do it *differently*, requiring a different mindset, different
structures, probably changed user interaction. It's a big transition. For some
resources to help with it see:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Short answer: Yes.

Real answer: Access is NOT "Excel on Steroids". It's not a spreadsheet; it can
certainly work with your data, and probably do everything that you're doing
now. But it will do it *differently*, requiring a different mindset, different
structures, probably changed user interaction. It's a big transition. Forsome
resources to help with it see:

Jeff Conrad's resources page:http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:http://allenbrowne.com/links.html#Tutorials

--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com
Click to expand...

Thanks john but what are the pros and cons of doing it? i see very
time consuming and boring to work with complex hlook up functions .
Also if one tab in sheet is broken, it affects everything. Is there
any application other than access which can do the job? What will be
the equivalent of if and sumiff and hllook up functions in access?
Thanks
 
Thanks john but what are the pros and cons of doing it? i see very
time consuming and boring to work with complex hlook up functions .
Also if one tab in sheet is broken, it affects everything. Is there
any application other than access which can do the job? What will be
the equivalent of if and sumiff and hllook up functions in access?
Thanks
Click to expand...

Oh, I'm fairly sure Access will be an excellent tool for this job, probably
better than Excel; you won't likely need anything equivalent to HLookUp since
Access, by its nature, is relational and will use Queries linking tables to
bring data from different "sheets" together.

HOWEVER... it's not going to be as simple as just coming up with the
"equivalent functions" or "same operations" in Access as you're currently
using in Excel. You will need to step back, reanalyze the task, restructure
your spreadsheets into properly normalized Access tables (very likely making
major changes to how the data is arranged), and then come up with ways to use
that normalized data to fulfill your business needs, using Access queries and
(probably) functions and VBA code. Without knowing the details of *how* your
spreadsheets manage the data, of course I can't say just *how* you would do
this - but I think you can and should do it, possibly with some professional
help if the structures are very complex.

Similarly, without knowing just what data you have or how your calculations
now work, I have no idea whether Access is "the best" tool or not. Someone
expert in Access may say, sure, bring it on; someone whose expertise is in VB6
or VB.Net or C++ may say "No way, my program is MUCH better" - and they might
be right!

But... based on what you've posted... it sounds like relating different blocks
of data to one another and doing calculations with them is at the root of your
task; given that, I'd strongly suggest that Access would be the first thing to
try. It's going to be a learning curve and an implementation effort, but I'm
guessing it will be simpler than most of the alternatives. Bear in mind my
Access bias and my ignorance of your detailed needs of course!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Oh, I'm fairly sure Access will be an excellent tool for this job, probably
better than Excel; you won't likely need anything equivalent to HLookUp since
Access, by its nature, is relational and will use Queries linking tables to
bring data from different "sheets" together.

HOWEVER... it's not going to be as simple as just coming up with the
"equivalent functions" or "same operations" in Access as you're currently
using in Excel. You will need to step back, reanalyze the task, restructure
your spreadsheets into properly normalized Access tables (very likely making
major changes to how the data is arranged), and then come up with ways touse
that normalized data to fulfill your business needs, using Access queriesand
(probably) functions and VBA code. Without knowing the details of *how* your
spreadsheets manage the data, of course I can't say just *how* you would do
this - but I think you can and should do it, possibly with some professional
help if the structures are very complex.

Similarly, without knowing just what data you have or how your calculations
now work,  I have no idea whether Access is "the best" tool or not. Someone
expert in Access may say, sure, bring it on; someone whose expertise is in VB6
or VB.Net or C++ may say "No way, my program is MUCH better" - and they might
be right!

But... based on what you've posted... it sounds like relating different blocks
of data to one another and doing calculations with them is at the root ofyour
task; given that, I'd strongly suggest that Access would be the first thing to
try. It's going to be a learning curve and an implementation effort, but I'm
guessing it will be simpler than most of the alternatives. Bear in mind my
Access bias and my ignorance of your detailed needs of course!
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com
Click to expand...

Thanks for your insights John once again. Can you please help me with
this offline? I will really appreciate it . I need it urgently or can
you atleast refer me to somebody? I am willing to pay for the time
used. Thanks again in advance . I am quite familiar with access. so it
should not take much time- but i need help in structuring and some
initial logic.

Thanks
 
Back
Top