Vlookup with variable column reference
I am trying to link two workbooks with a vlookup that searches for the last
populated columns' entry in a range (see example below).
27/03 28/03 29/03 30/03 31/03
Data 6 4
Is there a way of doing this in a function? In a macro I would use the
Range().End(xltoleft) code but I cannot figure out how to do something
similar in a worksheet function. I basically want the lookup column to start
at day 31 and make its way backwards until it finds a value. Another issue is
that the seemingly blank cells contain formula - ie. not empty.
1 ...variable column names in a report
i have an application where the user can enter self selected column names
which then in turn are added to a table... application works fine. however,
when it comes to reporting, how do i go about adding those self entered
columns in a report?
any help would be appreciated
Do you actually need this in a report or can the results be pushed to Excel
MS Access MVP
Help me support UCP http://www.access.hookom.net/UCP/Default.htm
"k2sarah" <email@example.com> wrote in message
news:C56B6FD1-2F5B-45D5-8760-A61E3CA41495@microsoft.c...Re: Massive "FIND" or " Compare cells " then , Delete....
lets say I have in Column A, Dan, John, Bill, Angela, Fred ...many
entries of the same names....
In Column B, I have Nancy, Dan, Steve,
I want to delete all of the "Dans" in both columns ...
Can I do something like: =compare( A1:B20 ) , delete,*.*))
I tried Subsitute, FIND, REPLACE....
I am Confused!!!!
Select A1:B100 (assuming that is your range), do Edit / Replace, replacing Dan
with nothing, then with the range still selected do Edit / Go To / Special /
Blanks, then Edit / Delete / Shift Cells UP.
This assumes you want ...Hide Cell Content if no data in previous columns
I've made this work many years ago but now can't remember
how. I have a job invoice to where the rate is
40.00/hour. This 40.00 is used in calculation to
determine the total amount of pay owed. (20 hours worked
* 40.00/hour). The boss requires that the hourly rate be
shown for each job on the invoice, so that means it is in
a column to where the amount is repeated all the way
down. All I want is for the contents of that particular
column NOT to show (show as blank), if no job has been
entered. In other words, if there is no data entered on
that particular row, hide the cont...Copy one Column Across One Row
I would like to take one column of data and copy it across one row.,
so, I want the data to go left to right across the spreadsheet instead of
top to bottom.
If you have a suggestion to solve this, macro or whatever, could you please
to me at:
If the column contains no more than 255 rows of data(256 if you are pasting
below row 256) you can copy it and select B1 and Paste
Gord Dibben Excel MVP
On Wed, 14 Apr 2004 17:23:46 -0700, <firstname.lastname@example.org> wrote:
&g...Totals in Stacked Column?
I can't display the totals in a stacked column chart. I'm trying to show the
total amounts for each column but the data labels only apply to each series.
Never mind - Andy Pope's website had the answer.
> I can't display the totals in a stacked column chart. I'm trying to show the
> total amounts for each column but the data labels only apply to each series.
...Trying to develop a shipping calculator to compare courier rates
I'm trying to develop a shipping calculator to compare courier rates. User
enters rates, and excel sheet calculates comparisons based on user defined
weight, # of items, destination and origin.
You mean the user has to enter in rate tables for each carrier at the
resolution of weight, destination, and origin. (and possibly number of
I suspect the user would prefer to call the carrier and ask them to quote a
rate, then select the lowest or if they have that information, just run
their finger down the paper table and figure out which is cheapest.
Usually the selling point of thi...Creating Labels, Missing Column
I'm trying to create address labels. In my file I have the columns for
"Street Address", "City", "State", "Zip Code" and "Country". The street
address does not show up in the window to select what info I want on the
label. Anyone know how to fix this???
In your table you "may" have IDfield, 1stName, 2ndName, Street Address,
City, State, Zip Code and Country.
Create a query based on the table. Bring all the fields in the query and
create a calculted culumn with something like
Create a new re...Clustered column graph with mixed data in 2nd column
I'm trying to create a clustered column graph with mixed data in the 2nd
column and I cannot find a way to do it. Any suggestions?
What's "mixed data"? Do you mean you want to build a stacked column for the
second column in the cluster? If so, look here:
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
"drhatt" <email@example.com> wrote in message
news:648BC5CE-4DE9-433C-A824-325B6995D...How to update a portion of rows and columns between two files
I have a Master file with 20 columns of data for each of approximately
700 rows. Each month, I receive an update file that I need to use to
update 10 columns of data for 3/4 of the rows in my Master file.
There is a unique identifier in each file. Not all rows in the update
file will be copied into the Master file and not all rows in the
Master file will have updates. What is the best way to do this? I am
familiar with VLookup. Shall I write aVLookup for each of the 10
columns in each row that need to be replaced so that I can grab the
cell from the new file? Or, is there an easier way?
...Columns on wrong side
When I open a book in excel, why is column A in Excel on the right side of the sheets, meaning it looks like this:
How do I change it to look the normal way from left to right when I open a new book in excel?
Seems you have set Excel to read from Right to Left - as is done in Arabic language.
To set it right
Go to menu Tools->Options
Choose the International Tab
In Right-to-Left Default direction choose Left-to-Right
The order of column will be A-B ...
> When I open a book in excel, why is column A in Exce...Checkbox column #2
How do I create a checkbox column to contain boolean values ?
"Bo Rasmussen" <firstname.lastname@example.org> wrote in news:OtsOMgl6DHA.3648
> How do I create a checkbox column to contain boolean values ?
Excel doesn't support this directly. To get a checkbox you need to use VBA
Forms or OLE controls, create a bunch of checkboxes, then link them to the
cells. Check out View/Toolbars/Forms and View/Toolbars/Control Toolbox.
Could you describe in more detail what you're looking for?
My email address has an extra @ (s...Wrapping text in column
In Excell 2007, how do you set a column to wrap text?
I tried it and even though it seems to allow me to do it when I select
column and check wrap text from Format Cells, it doesn't work. It only
works when I select one cell and select wrap text.
This is a bit irritating as I have many cells in a column to do this to.
I find that this happens when I paste from Word. If I have the fields set
as Vertical/Top and Wrap Text - then I paste any data from Word, it goes to
Vertical/Bottom and NOT wrap text.
If I paste data from my Textpad program that spans multiple line...How do I make multi column stacked graphs in Excel
See the links on Jon's page for Cluster Stacked charts.
Ed B wrote:
...Subtotaling on column with IF statement results
I created a spreadsheet with one column having an IF statement - it
calculates how much we should re-order per store based on what was sold.
I asked it to subtotal for each store - the formula appears in the cell, but
it does not calculate the subtotal. I tried saving the column as the values,
but it still does not subtotal. When I typed in the numbers, it DID
subtotal. What do I need to do so it will subtotal?
Thanks for your help.
By any chance are you importing the data from another application?
Check out your numbers: does the cell contain an apostrophe and then
the numb...Comparing data sets with +/-1 tolerance using conditional formatin
Please help! This has been puzzling me for hours!!
I am using conditional formating to compare one data set with another to see
if there are any discrepancies, using green cells for the same and red cells
for different data.
However, one set of data is rounded to the whole number, but the other set
is to 2 decimal places (and has to remain that way). Therefore I need my
conditional formating to compare the two data sets, but with a tolerance of
plus or minus 1.00. How do I create this formula using conditional formating?
CF/ Formula is: =ABS(A1-B1)<=1
...how do you compare two dates to find the difference?
mm/dd/yyyy is the format. I need to subtract two dates to find the difference
and format the result as 'Number'
> mm/dd/yyyy is the format. I need to subtract two dates to find the
With dates in A1 & A2, in A3:
This will give you the number of days difference. Format A3 as general
"Todd" <Todd@discussions.microsoft.com> wrote in message
> mm/dd/yyyy is the format. I need to subtract two dates to find the
...Lines Between Columns
I have a newsletter page that is formatted to be double column. I want to
place a 0.5 point line between the columns.
Right clicking within the double columns then clicking on format text
box>colors and lines then clicking the box in preview that is for the center
line only nothing happens to the menu on the left. The "dashed", "style" and
"weight " boxes are grayed out.
Out of the six boxes shown there, the only two that activate the above
quoted boxes are the two on the right of the bottom row (frame and frame
with line down the middle).
I ...stop returning column headings from DB query
I put together a query to go against Oracle that returns a block of data to
the spreadsheet. Is there an option somewhere to have the "Return Data to
Excel" do so without also returning the column headings? I want the data
thanks - Russ
There are 2 places that you can change that setting.....
1)When you return data from MS Query the dialog where you select the
location for the data has a Properties button.
Click that button
UNcheck: Include Field Names.......click [OK]
Continue selecting the query destination
2)After the data has been pulled from Oracle
Right-click...Incrementing/decrementing column characters using only worksheet functions?
How can I increment and decrement column characters/letters using
I have a list of 5 characters corresponding to columns.
The first character might be A, for column 1.
How can I get the second character in the list to automatically
configure itself to be B, the third C, the fourth D and the fifth E?
That is, I want to set this up so that the second character is linked
to the first, the third to the second and so on.
That way if I change the first character from A to D then the second
character in the list will automatically become E, the third will
change to F, the f...Locking rows (not columns)?
I download and store my bank statements in an Excel spreadsheet. This
is maintained in conventional 'bank statement' format; ie columns for
date, payee, amount, account balance, with one statement entry per row
in chronological order. I find it very useful to be able manipulate
the spreadsheet eg by sorting alphabetically by payee, so I can easily
see all payments made to a particular company grouped together.
However, it's very easy to use the 'sort' function incorrectly, so
that the date/payee/amount columns become completely dissociated from
each other, and if some...Unhide Columns A and B.
I've somehow hidden columns A and B. I followed the directions in the help
section but it did not work. How can I unhide them?
try putting cursor on cells (on top of the row numbers)>format>column>unhide
"Dan" <Dan@discussions.microsoft.com> wrote in message
> I've somehow hidden columns A and B. I followed the directions in the
> section but it did not work. How can I unhide them?
Thanks for the quick reply, but its still not worki...Eliminate unused columns
I have a table of columns of data per week, which I convert to a table of
columns of data per month. This leaves 3 or 4 blank columns between the
months. Is there a simple way to consolidate the month columns into a
contiguous region, eliminating the unused columns between the month columns?
Or... is there a smarter way to do the conversion than sum across an fill
One approach is to use a macro:
if your empty columns are completely empty you could use this macro. Hit
Alt-F11, Click Insert/Module (check the project window to make sure your
workbook is the active project before...Can I put a secondary axis on a 3-D column chart in MS Excel?
Can I put a secondary axis on a 3-D column chart in MS Excel? If so, How?
I want to over lay a line on top of a 6x12 row set of 3-D columns.
I don't know about a secondary axis, because I avoid 3D charts. You cannot
make a combination 3D chart, though, so your plan of a line with the columns
will not be possible.
Use a clustered 2D column chart, and add a line or XY series:
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
2006 Excel User Conference, 19...titling across columns
I want to format my title paragraph to cross the columns in the text box. I
cannot find how to do this. Publisher 2007.
A separate text box?
A Design Gallery object such as a Masthead?
"Solomod" <Solomod@discussions.microsoft.com> wrote in message
>I want to format my title paragraph to cross the columns in the text box. I
> cannot find how to do this. Publisher 2007.
> Any ideas?
> Many thanks,