Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define?
More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C).
In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to
turn it off.
Anyone know if there is a way to reset this so that newly pasted text will
not continue to get broken up (for example by the space delimiter)
Presently the only way is to exit Excel and restart Excel - then pasted text
all goes into one cell regardless of spaces.
Hope I explained that well enough
I may have been to hasty in making this assumption, it appears that the
problem I described below is only happening on one workstation - this may
indicate that the Excel Registry keys are in need of...Column Headings #11
Can you add seperate column headings (A, B, C, ...) into one spreadsheet?
I'm attempting to alter the column sizes half-way through the spreadsheet
w/out affecting the upper column sizes...
Coolumn width belongs to the entire column and cannot be altered in separate
sections of that column.
Gord Dibben Excel MVP
On Tue, 8 Mar 2005 15:51:01 -0800, spencer4hire
>Can you add seperate column headings (A, B, C, ...) into one spreadsheet?
>I'm attempting to alter the column sizes half-way through the spreadsheet
>w/ou...WM_QUERYENDSESSION and saving data through a worker thread
I have an application that uses a worker thread to save/load data.
I'm wondering what is the best reaction to WM_QUERYENDSESSION in my case.
I have to possible scenarios:
1. When WM_QUERYENDSESSION comes fire the thread and wait for it to end.
Only then return TRUE from WM_QUERYENDSESSION handler.
The problem is that I will get nusty dialog that my application "is not
2. When WM_QUERYENDSESSION comes fire the thread and return FALSE from the
handler. When thread is done force application to end. But this way I will
probably prevent Windows from closing,...Copying data from one chart to another
I have many graphs - all plotting on similar scales but using different
data. Is there any way I can simply copy one set of data from one graph
and paste it into another graph so that I can avoind going through all
the hassle plotting each curve again? I want to have graphs showing
different combinations of the same data and have hundreds of curves to
plot so this could be a huge timesaver...
Alan_Partridge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29295
V...How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets
within a workbook each paste will assume the Text->Column formatting that I
applied in the previous. How can I prevent it from happening ?
Just run another data|Text to columns against a dummy cell.
Specify delimited, but remove all the check marks from all the possible
(alternatively, you can close excel and reopen it.)
> I've noticed in Excel 2000 that if I paste text into various worksheets
> within a workbook each paste will assume the Text->Col...Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is
there a way to abbreviate this value so the other data points show better in
One way is to break the Y axis, have a look at these examples of how to
> I've got a chart where one value (8,300) greatly exceeds all the others. Is
> there a way to abbreviate this value so the other da...Excel 2003 Copy/Paste filtered column
I have a filtered column on my spreadsheet. I have copied the column,
changed the figures and then tried to paste it back on to the filtered
column. It is not copying over the original filtered column but rather over
cells that have been filtered out. The worksheet/cells are not protected.
What could the problem be?
That's the way pasting works. It'll hit the visible and hidden cells.
> I have a filtered column on my spreadsheet. I have copied the column,
> changed the figures and then tried to paste it back on to the filter...Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a
cell or cells selected. But when you are in a Pivot Table or have an entire
column selected the right-click popup is different.
Is there a way to add an item to the right-click popup menu when you are in
a Pivot Table or have an entire column selected?
Thank you for your help.
Never mind. This one was right in the help section. I should have looked
> I have added items to the right-click menu that popups up when you have a
...how to automatically suppress space before after column break?
Having Spacing Before and After on some of the styles, I seem to be unable to
have the space before at the beginning of a column automatically dismissed
when applying a column break. I have tried a couple of options under
compatibility, but to no avail. This in on Word 2003. The No HTML function +
No Space Before after column break do not solve the problem. Can you help
Tools | Options | Compatibility: Suppress Space Before after a hard page or
column break. If this isn't working, then check to make sure you don't have
an empty paragraph before the first text pa...matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has
500 numbers, Column B has 1000 numbers. I need to know which cells in
Column A have a match in Column B, and if so, what is the Cell (or row
number) in B that matches to that particular cell in A. How can I do
Thank you for your help.
** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
try the following:
- insert a new column between A and B (so make B the new C column)
enter the following in B1
=IF(ISNA(MATCH...How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into
Excel 2003. How do I do this and maintain my formulas and the individual
if the lotus file is a wks version or earlier, xl should open it and let you
save it as an xl file.
if the lotus file is a 123 version or higher, you can open the file in lotus
and save it as an xl file.
if you don't have lotus, find someone who does.
> I am trying to convert several complex Lotus 123 workbooks with formulas into
> Excel 2003. How do I do this and maintai...obtaining data in text form from a table
I like to be able to obtain the dates in a text format from the table
Test6 4-Feb,5-Feb, 9-Feb
Do I need to do this by macros and if so, any help would be appreciated.
Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb
Test5 4-Feb 8-Feb
Test6 4-Feb 5-Feb 9-Feb
Vlookup should do what you want, as in:
Adjust the ranges t...Determine number of rows with data
I am using the macro below to pull some data from an external workbook.
The 2 issues I need to sort are:
1. The number of rows in the external workbook can vary. How do I amend this
code to pull all of the rows with data?
2. The number of rows in the autofill also may vary. How do I autofill only
the number of rows required? i.e the number of rows in column A that contain
'Lookup Previous Month Sales
Selection.NumberFormat = "General"
Selection.FormulaArray = _
"=S...Count the text in a column
I would like to count the text in a column then for it to add a figure in
another cell if it meets the text criteria
Do you mean count the characters?
as an array formula (committed with Ctrl-Shift-Enter)
(remove nothere from the email address if mailing direct)
"Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message
> I would like to count the text in a column then for it to add a figure in
> another cell if it meets the t...Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I
delete data without deleting formulas?
You could try this
tap F5 - Special - Constants - OK
and if that selects the data you want to delete then tap the delete key
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
> When I hit delete contents all data and formulas are deleted. How can I
> delete data without deleting formulas?
First use Find...forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row
when you have reached the specified maximum number of characters in the row
there's n o bulit-in feature for this
"Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag
> Is there a way to have excel do an auto "carriage return" to the next row
> when you have reached the specified maximum number of characters in the
...Add data to cell w/o loosing initial data
I would like to know if there is a way to add data to data without retyping.
For example I have a colum of 18015555555 and I want to add [rfax:(cell
#)@/fn=(phone number)] So I would like to add the brackets - copy from a
cell - @/fn= and not loose the data already in the spread sheet. Example 2.
Add [rfax:company name@fn/=(saved data here) then close bracket.
So I want to add data to cells without loosing the data already in the
cells. I have about 600 of them to do and I really don't want to do each one
Please let me know if anyone knows how to accomplish this.
Tha...Removing filters from data
I have recorded a macro to remove filters from data lasts in advance of
performing other actions.
However if the data is unfiltered the macro falls over with the message
Run time error '1004' ShowAllData method of Worksheet class failed.
I think I need some sort of if error continue code or something to check
I would be grateful if someone could point me in the right direction please.
If Activesheet.Filtermode Then ActiveSheet.ShowAllData
"Philip J Smith" wrote:
> I have re...Looking up and matching data
I have two sets of data with the same information but not in the same order
and am trying to match the data. In each data set I have 10 pools containing
100 loans. Each pool has a unique ID and each loan within the applicable
pool has an ID of 1 to 100. I need to look up the Pool ID, then look up the
loan ID so that I can extract the property type information from a third
column. The Pool ID and property type is text but the loan ID is a number.
I am struggling to put together the right combination of formulas to give
the property type for each loan within each pool. Any suggestion...Selecting a column with an integer
' Selecting a column with an integer
' Please show me how to eliminate the use of Cells(1, 1)
Dim r As Integer
Dim c As String
Dim numericcolumn As Integer
Dim alphabetcolumn As String
numericcolumn = 4 ' in practice 4 is the
resultant of an equation
alphabetcolumn = "=CHAR(" & numericcolumn + 64 & ")"
Cells(1, 1) = alphabetcolumn ' I like to eliminate the use of
c = Cells(1, 1).Value ' I like to eliminate the
use of Cells(1, 1)
Cell...invisible listbox data
I have developed software in Acces XP that is distributed to two different
locations and I have noticed some odd behaviour at one of the locations.
Sometimes the data in listboxes or combo boxes is invisible. The data IS
PRESENT because you can select and use the records as before (although you
can't see which ones you're selecting). The combo boxes are poplulated and
have the correct dropdown length for the records one would expect. The
listboxes have scroll bars where one would expect a scrollbar and
multiselcetion is possible where apropriate.
I have played around with th...Moving certain data to different sheet
I need to move data that meets a certain criteria, to another sheet within a
workbook. For instance, if a column of data is for a certain ZIP code area, I
need it to automatically copy to a sheet for that city. Say, 40202 would go
to the Louisville, KY sheet. Because Louisville has multiple ZIPs, I would
need only the data that begins with 402 to go to that sheet. Lexington KY's
data, which begins with ZIP code 405, would go to its own sheet. Macro?
This can definitely not be created with a formula. I suggest that you make
use of the macros.
Kris...BP Req Mgmt Lookup should show additional columns
When doing a lookup I should be able to configure the columns that I would
like to see visible on the lookup. For example, when looking up an item only
item number and item description are visible fields. I would like to
configure the lookup to show additional fields, like the vendor name.
...Prevent copy and paste in one column
I am having trouble trying to prevent copying and pasting in one specific
column. The code refers to the specific range, but yet it prevents copying
and pasting on the whole worksheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Columns("H:H")) Is Nothing Then
Application.CellDragAndDrop = False
Application.CutCopyMode = False
Application.CellDragAndDrop = True
Works for me in Excel 2003
Gord Dibben MS Excel MVP
On Fri, 30 Apr 2010 11:42:01...