import excel in access only if data not if lookup from other sheet

[ano]

i have a spreadsheet looks like a crosstab dates/names-->money
the money comes from copy/paste from internet in new sheet named date of copy
the names are on the top row identical the new sheet has
and the dates are identical new sheet name
so every time i get new sheet
i copy last row to new row and adjust few cell to meet new sheet
this way no much data entry
but need keep all sheet from internet otherwise the cell got error/empty
because the first year not so advanced i did not notice the problem
when the database looks fine the total amount was wrong.


then i found the many not filled
is there a way to read/copy the cell by value?to fill access table?

why only the top row as heading why not the frozen top?

[CJ_London]

Not much detail so best guess is using a union query to append column by column

tip for the future - databases are about data and example data is better than a thousand word description to describe a) what you have now and b) what you want to end up with. Otherwise we are all guessing and it gets very tiresome trying to get clarity to the extent some responders won’t bother. Responders are all unpaid volunteers and like to make best use of their time

[xps35]

This question is even more unclear than your previous one. At least then you made an attempt to make something clear with attachments. If you don't respond to requests for additional information, I'm afraid we won't get any further.

[ano]

sorry forgot , the problem is ws "total" hardly data only the top is data

[CJ_London]

still don't understand the problem. columns do not have headings. No explanation of which columns need to be copied. And no idea to where - is this an excel question or an access one?

what does 'many not filled' mean?
what does 'why only the top row as heading why not the frozen top?' mean?

What is this even supposed to do? You copy something from the internet - to where? what are you then doing with the data?

[mike60smart]

It would help if you told us what each Column Represents in the Total Sheet ?

[ano]

- the spread sheet need from crosstab back to 1 table naam(top),date(left),money solved after new sheet only values
- its an access problem that it did not import the values solved in excel
- its lot of work to change all columns to currency during import !
- normally import should make import 1-255, 256-510,511-765 ... automatically or after confirm so no data lost
- many not filled bec no values only formule lookup solved
- frozen is possible more rows i used 2 rows, so row 2 was rejected solved in excel
- the money is in dollars and they need pesos, so they can know the earn
seems the import is not often used, because crosstab is common in excel i guess so import should also split the crosstab.
the examples from the syntax are too simple to understand the syntax.
thnx for the help , hope access improve because large excel will be more easy in access special if many to many is supported
after excel i expected perhaps to much
tc enjoy the new year

[ano]

ur right but then lots of extra work because top row contains heading spreadsheet heading crosstab heading nick name
but simple
date , exchange , total, [ dollar , dollar * exchange , dollar * exchange - sum advance] for each person.
the dollar is lookup from sheet(date) copy/past from internet, so very little typing to do
tnx i think all is solved in excel, but no internet by broken cable a week rest
tc enjoy the new year

[mike60smart]

Hi
So are you saying that all you are trying to do is Import the "Total" Sheet ?

[CJ_London]

hope access improve because large excel will be more easy in access special if many to many is supported
after excel i expected perhaps to much

access and excel are two different applications and work in different ways. Excel stores data short and wide, databases tall and narrow. Excel combines data and presentation in one view, databases stores data in tables and uses queries/forms/reports for views. As for increasing the number of columns, don't hold you breath. Or use sql server express which has a maximum columns of 1024.

Perhaps if you made your excel sheet simpler, it would be less of a problem. Maybe instead of

-

you had something like


You can then pivot the data for the crosstab view you want in excel.

or simply use access to get the data from the individual files rather than via an excel interpretaion

[ano]

Hi
So are you saying that all you are trying to do is Import the "Total" Sheet ?

no mike i try to change from excel to access to need less cpu/memory
so excel copy/paste internet and import in access and add to total-->tables : exel ,rate
advance , abc is simple manual input
just did not expect that i need 1st copy sheet.value before import into access

[ano]

im not going back to excel layout and certainly not a layout like above even in database this is a complex layout with no addinional meaning/reading.
i was thinking forms with name,date,dollar (order by date desc,name asc) to select from seeing whole list (option: filter,search,select) opening on load at most recent date on 1st alphabetic order name with 2nd tabel (rate) sync with 1st table and 3rd table (advance) sync with 2nd table using abc.
subforms should not be needed, (hard to test separately , no extra value) perhaps need extra textbox to display clearly the name and amount. the one to many is not easy to select. and blocked changes.
tnx for the suggestion but prefer a healthy database with new layout. without being forced to different forms to add/change tables

tc enjoy the new year

[mike60smart]

What table have you created in Access to manage this process?