I have a .NET application that, among other things, creates Excel
workbooks, and I have run into a very strange problem involving
formulas on one worksheet that reference values on another worksheet.
The text I write into, let's say, cell A25 on Sheet1 (using .NET)
looks something like this:
=VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
On the completed workbook this turns into:
=VLOOKUP(H25,'Sheet2'!A:X,6,FALSE)
This formula works fine when I type it in by hand, and I've been
using the RC[] syntax in .NET without trouble for quite a while
(although up until now all my formulas have only referenced their
own worksheet). But when I create this exact same formula using
..NET I get a #NAME? error!
There is nothing wrong with the text in the cell. Forcing the
workbook to calculate using F9 doesn't help. But if I double click
on the cell as if I were going to add something to the formula,
and then move away without adding anything, suddenly the formula
works! (There is further weirdness involving what happens if I
now try to drag the apparently working formula across other cells,
or when I save the workbook, but it's kind of hard to describe, so
I won't go into it here unless someone wants to know).
I've been able to get around the problem for now by using a named
range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous
about this, and I need to understand what is going on. I have not
been using the Formula or FormulaR1C1 properties to create formulas
-- so far I've just been assigning text to a cell, and nothing more
has seemed necessary, even for formulas. Could that be what's
causing the problem in this case, when the formulas involve another
worksheet? And if not that, then what might it be?
--
John Brock jb****@panix.com 4 1776
I would guess it's because you're using a combination of R1C1 reference style
and A1 reference style.
=VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
should be more like:
=VLOOKUP(RC[7],'Sheet2'!c1:c24,6,FALSE)
C1:C24 is column 1 to column 24.
John Brock wrote:
>
I have a .NET application that, among other things, creates Excel
workbooks, and I have run into a very strange problem involving
formulas on one worksheet that reference values on another worksheet.
The text I write into, let's say, cell A25 on Sheet1 (using .NET)
looks something like this:
=VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
On the completed workbook this turns into:
=VLOOKUP(H25,'Sheet2'!A:X,6,FALSE)
This formula works fine when I type it in by hand, and I've been
using the RC[] syntax in .NET without trouble for quite a while
(although up until now all my formulas have only referenced their
own worksheet). But when I create this exact same formula using
.NET I get a #NAME? error!
There is nothing wrong with the text in the cell. Forcing the
workbook to calculate using F9 doesn't help. But if I double click
on the cell as if I were going to add something to the formula,
and then move away without adding anything, suddenly the formula
works! (There is further weirdness involving what happens if I
now try to drag the apparently working formula across other cells,
or when I save the workbook, but it's kind of hard to describe, so
I won't go into it here unless someone wants to know).
I've been able to get around the problem for now by using a named
range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous
about this, and I need to understand what is going on. I have not
been using the Formula or FormulaR1C1 properties to create formulas
-- so far I've just been assigning text to a cell, and nothing more
has seemed necessary, even for formulas. Could that be what's
causing the problem in this case, when the formulas involve another
worksheet? And if not that, then what might it be?
--
John Brock jb****@panix.com
--
Dave Peterson
Check your other post.
Dave Peterson wrote:
>
I would guess it's because you're using a combination of R1C1 reference style
and A1 reference style.
=VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
should be more like:
=VLOOKUP(RC[7],'Sheet2'!c1:c24,6,FALSE)
C1:C24 is column 1 to column 24.
John Brock wrote:
I have a .NET application that, among other things, creates Excel
workbooks, and I have run into a very strange problem involving
formulas on one worksheet that reference values on another worksheet.
The text I write into, let's say, cell A25 on Sheet1 (using .NET)
looks something like this:
=VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
On the completed workbook this turns into:
=VLOOKUP(H25,'Sheet2'!A:X,6,FALSE)
This formula works fine when I type it in by hand, and I've been
using the RC[] syntax in .NET without trouble for quite a while
(although up until now all my formulas have only referenced their
own worksheet). But when I create this exact same formula using
.NET I get a #NAME? error!
There is nothing wrong with the text in the cell. Forcing the
workbook to calculate using F9 doesn't help. But if I double click
on the cell as if I were going to add something to the formula,
and then move away without adding anything, suddenly the formula
works! (There is further weirdness involving what happens if I
now try to drag the apparently working formula across other cells,
or when I save the workbook, but it's kind of hard to describe, so
I won't go into it here unless someone wants to know).
I've been able to get around the problem for now by using a named
range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous
about this, and I need to understand what is going on. I have not
been using the Formula or FormulaR1C1 properties to create formulas
-- so far I've just been assigning text to a cell, and nothing more
has seemed necessary, even for formulas. Could that be what's
causing the problem in this case, when the formulas involve another
worksheet? And if not that, then what might it be?
--
John Brock jb****@panix.com
--
Dave Peterson
--
Dave Peterson
Sorry.
I didn't notice that the original was cross posted to all these groups.
Dave Peterson wrote:
>
Check your other post.
Dave Peterson wrote:
I would guess it's because you're using a combination of R1C1 reference style
and A1 reference style.
=VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
should be more like:
=VLOOKUP(RC[7],'Sheet2'!c1:c24,6,FALSE)
C1:C24 is column 1 to column 24.
John Brock wrote:
>
I have a .NET application that, among other things, creates Excel
workbooks, and I have run into a very strange problem involving
formulas on one worksheet that reference values on another worksheet.
The text I write into, let's say, cell A25 on Sheet1 (using .NET)
looks something like this:
>
=VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
>
On the completed workbook this turns into:
>
=VLOOKUP(H25,'Sheet2'!A:X,6,FALSE)
>
This formula works fine when I type it in by hand, and I've been
using the RC[] syntax in .NET without trouble for quite a while
(although up until now all my formulas have only referenced their
own worksheet). But when I create this exact same formula using
.NET I get a #NAME? error!
>
There is nothing wrong with the text in the cell. Forcing the
workbook to calculate using F9 doesn't help. But if I double click
on the cell as if I were going to add something to the formula,
and then move away without adding anything, suddenly the formula
works! (There is further weirdness involving what happens if I
now try to drag the apparently working formula across other cells,
or when I save the workbook, but it's kind of hard to describe, so
I won't go into it here unless someone wants to know).
>
I've been able to get around the problem for now by using a named
range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous
about this, and I need to understand what is going on. I have not
been using the Formula or FormulaR1C1 properties to create formulas
-- so far I've just been assigning text to a cell, and nothing more
has seemed necessary, even for formulas. Could that be what's
causing the problem in this case, when the formulas involve another
worksheet? And if not that, then what might it be?
--
John Brock jb****@panix.com
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
By golly, you seem to have nailed it! I tried your suggestion and
it worked. Thanks! (It created a $A:$X reference in the formula
rather than A:X, which of interesting, but doesn't seem to matter).
I'd still like to understand what sort of state I was putting the
workbook into though. It really was pretty strange.
I also wish I knew whether I really ought to be using the FormulaR1C1
property, instead of just writing strings beginning with "=" into
cells. It wouldn't be that easy, since for efficiency I'm actually
slapping a two dimensional .NET Object array onto a large region,
rather than writing cells one at a time, so if I wanted to use
FormulaR1C1 I'd have to go back afterwards, find the formulas, and
do them all again the right way. What a pain! You think I can
continue to get away with what I am doing now?
In article <48***************@verizonXSPAM.net>,
Dave Peterson <pe******@verizonXSPAM.netwrote:
>I would guess it's because you're using a combination of R1C1 reference style and A1 reference style.
=VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
should be more like:
=VLOOKUP(RC[7],'Sheet2'!c1:c24,6,FALSE)
C1:C24 is column 1 to column 24.
>John Brock wrote:
>> I have a .NET application that, among other things, creates Excel workbooks, and I have run into a very strange problem involving formulas on one worksheet that reference values on another worksheet. The text I write into, let's say, cell A25 on Sheet1 (using .NET) looks something like this:
=VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
On the completed workbook this turns into:
=VLOOKUP(H25,'Sheet2'!A:X,6,FALSE)
This formula works fine when I type it in by hand, and I've been using the RC[] syntax in .NET without trouble for quite a while (although up until now all my formulas have only referenced their own worksheet). But when I create this exact same formula using .NET I get a #NAME? error!
There is nothing wrong with the text in the cell. Forcing the workbook to calculate using F9 doesn't help. But if I double click on the cell as if I were going to add something to the formula, and then move away without adding anything, suddenly the formula works! (There is further weirdness involving what happens if I now try to drag the apparently working formula across other cells, or when I save the workbook, but it's kind of hard to describe, so I won't go into it here unless someone wants to know).
I've been able to get around the problem for now by using a named range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous about this, and I need to understand what is going on. I have not been using the Formula or FormulaR1C1 properties to create formulas -- so far I've just been assigning text to a cell, and nothing more has seemed necessary, even for formulas. Could that be what's causing the problem in this case, when the formulas involve another worksheet? And if not that, then what might it be? -- John Brock jb****@panix.com --
Dave Peterson
--
John Brock jb****@panix.com This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dan |
last post by:
Hello,
I am trying to read and write to an Excel file via my Java applet. I
have done so successfully on several simple Excel files that simply
had data cells without many complicated equations...
|
by: David Alliet |
last post by:
Hello,
I'm having a bit of a problem with ASP and Excel. A client has developed
his own program, which calculates alot of financial stuff, in excel. I'm
doing his websites and he has requested...
|
by: Steven Stewart |
last post by:
I have a user who has been using Excel for a while to keep statistics
and print reports. She finds using it cumbersome because of long
formulas and a lot of copying and pasting.
I have designed...
|
by: Aaron |
last post by:
Hello,
I have a small application that I need to save data from 7 text boxes in to
a csv file. This will entail btnNext_Click function that will create a new
csv file and enter the 7 data fields...
|
by: Steve Chatham |
last post by:
I use the following code:
Private Sub RbtnExport_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles RbtnExport.SelectedIndexChanged
Dim sFile As String =...
| |
by: Carlos Magalhaes |
last post by:
Hey All,
I am doing some excel automation using the excel COM. I can do most of
the functions and its working well until I come across a formula.
I can run a formula and insert the formula...
|
by: RJN |
last post by:
Hi
I've a template excel file which has all the calculations defined. There
are certain input values to be entered which gives a lot of output to
the user. I don't want to expose the excel sheet...
|
by: toffee |
last post by:
Hi all,
I created a little button which when clicked will run a query on mysql db
and output the results as an excel spreadsheet.
I do this by setting the header as application excel. All works...
|
by: Duncan Smith |
last post by:
Hello,
I am currently implementing (mainly in Python) 'models' that come
to me as Excel spreadsheets, with little additional information. I am
expected to use these models in a web application. ...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |