spreadsheet

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
 
J

John W. Vinson

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

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
 
L

lee

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

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
 
J

John W. Vinson

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

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
 
L

lee

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

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
 
B

Bob Phillips

Personally, I can't see why you would want to use Access, Excel is a far
superior tool.

If you have a lot of data and ytou want to separate the data and the code,
that is a smart move, and can be achieved by either having an Excel data
workbook or an Access database, and an Excel code workbook. You could use
the code workbook to get the data and display it and then do any data
related manipulations. The advantage of this approach is minimal changes,
you can probably use those same lookups etc once you have pulled in the
data. And then, at your lesiure you can convert it to a more maintainable
application, even do more in Access if you really have to. But I would
caution you very strongly against have calculated or deducted data in your
database/data-store, you should do that sort of work in the presentation
layer.

HTH

Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top