excel spreadsheets into access

[Nydia]

I maintain 6 spreadsheets (2 tabs in each spreadsheet). The spreadsheets
consist of various codes (about 400) and a bunch of formulas. Each formula
has different codes. I need to combine all of the spreadsheets, but there
isn't enough columns in a spreadsheet. I'm thinking putting it into a
database would work. THe problem is, I'm not sure how to do this. Below is
an example of how the spreadsheets are set-up

Codes Formula1 formula2 Formula 3
1 50% 35%
2 50% 75% 30%
3 25% 35%

 

[Pat Hartman \(MVP\)]

1. A database is intended to store data rather than logic. A spreadsheet
combines both in a single place. In a database, tables hold data and
queries, macros, and VBA provide the logic (formulas).
2. Access tables are limited to 255 columns so if you have too many columns
for a spreadsheet, you have way too many columns for a table. You'll need
to normalize your table.

I can't tell by your example what the columns represent so I don't know if
what I am suggesting will make any sense but to convert your spreadsheet to
a table, you'll need to turn columns into rows:

1 Formula1 50%
1 Formula3 35%
2 Formula1 50%
2 Formula2 75%
2 Formula2 30%
3 Formula2 25%
3 Formula3 35%

Once the data is stored in this fashion, you can use crosstabs to pivot the
data to it replicates the original spreadsheet look --But-- you are still
limited to 255 columns at a time.

 

[Nydia]

Let me see if i can explaine it better. We have 407 codes and these codes
are used for all of our formula sheets. Each formula sheet uses different
codes. So one formula code have 10 codes in it and another could only have 1
code. vice verse, one could could be in >5 formulas or it can be in only 1.

So there are 6 spreadsheets, each spreadsheet represents a product line (ex.
soldering flux is one, paste is another) and there are 2 tabs each tab
represents the location (Norht America, Asia Pacific). So all the
spreadsheets are pretty much the same except the top rows have the formula
names with are different for each formula.

What I want to do is see how many formulas are in each code. But i can't
figure out how to do it. Does that make sense?

 

[Pat Hartman \(MVP\)]

Are you using the word formula to mean a calculation that is stored in the
spreadsheet or are you using the word to mean the name of something like
"Diet Pepsi Lime" or "Comet Cleanser" or "Windshield washer fluid".
From what you have added to your description, I would add two columns to the
table I suggested - ProductLine and Location.