copy values from a several cells into one cell in another sheet

[mar7632]

Hello everyone,

I have a problem in which I am stuck. I have two sheets in a Excel which they are used to introduce some data in the first sheet and then copy that data to the second sheet which will be transfer to Access. The second sheet is organized in columns and one by one cells for each column while the first sheet has a structure like the image I have uploaded.

In order to copy some value from Sheet1 into Sheet2 what I need to do is to specify like this: (from Sheet2 I select cell A1 and write) =+'Sheet1'!A2 and the value appears. Ok

But in my case I get an error called #VALUE! if i want to select a certain value from Sheet1 which is combine by 2 or more cells. What I do is to write in the second sheet this: =+'Sheet1'!E9:F9

What should I do in order to solve this problem? Because some days before I was doing the same process and it was fine.

Also, I have a field (the image I upload) which has several rows and columns. In that case, how do I extract that value and put it on a certain cell in the second Sheet?


Thank you in advance,

Regards.

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[mar7632]

Thank you answering AliGW,

I have been doing some tests and what I found is that the Excel itself is like "corrupt". I have copied the Excel by ctrl + V into another new Excel and it worked out.

I will try again, if the error is kept going, I will reply again to this thread.

Regards.

[mar7632]

Hi again,

I have solved the problem by changing the Excel book by a new one.

But I have a doubt related to those data which are transferred to the second sheet. If I do not introduce any data in the first sheet (blank) it will give the value zero "0" in the Sheet2.

I have changed my settings by hiding my zero values and convert it into null values. However, the problem I get when I go to my Access program and export all the excel data is that those "null" values are recognized as zero values instead of blank-null values.

The article I used is from Excel Support. "show or hide zero values"

How should I do?

Thanks in advance.