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 ...conditional summing of arrays
I would like to sumproduct 2 arrays up to the row where it first exceeds
a given number. For example assume 2 arrays are as follows : 1,2,3,4,5 &
2,3,4,5,6 the sumproduct is 2,8,20,40,70. So if my given number was say
30, the answer would be 4. if the number was say 80, the result would be
NA. Can anyone help me with this problem? Thank you and kind regards JV
** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
This can probably be done easier
=INDEX(LARGE(ROW(INDIRECT("1:"&...Conditional format to cells containing a #DIV/0! error
I would like to apply a conditional format to an entire spreadsheet.
In particular, if a cell returns and error such as #DIV/0! I would the
selected font color to be white.
What do I have to enter in the conditional formatting menu? If I set
the value of the cell equal to #DIV/0!, the format does not work.
A solution that I found could be to use the GoTo functions to select
the cells with errors and then manually apply the color, but since I am
creating a template at a later point the empty cells that return the
divided by zero error may contain a number. Basically I would like the
cells t...vlookup from two sources
Is it possible to use syntax to perform a vlookup from a source and then
another if the first is False?
My primary table of data is called "Materials" and my second source is a
range A81:E140 on the same sheet as the vlookup,
So something like :
Can anyone help with advice on syntax?
the false in a Vlookup is the mtach type, not an value to use if the formula
=if(iserror(vlookup(A1,Materials,2,false)),Vlookup(A1,A81:E140,2,false),Vlookup(A1,Mate...Can't open two Documents with same name !!
Hi: I install Office 97 in WinXP, works good except when I
open a excel spreadsheet a windows always opens saying
Can't open two Documents with same name. I click ok and
everything works OK. How do I stop this warning I get all
the time when I first open a Spreadsheet. Thanks
Debra Dalgleish has a list of frequently asked questions at:
This one sounds like yours:
> Hi: I install Office 97 in WinXP, works good except when I
> open a excel spreadsheet a windows alway...keeping two computers updated with one money file
I use a desktop at home and a laptop when I am on the road. What I would like
to do is be able to keep my money file syncronized between my two computers.
Money offers nothing that will help with this. There is no good way to do
this short of something like WinXP Synchronization Manager. I can't say as
I've tried it or read here of someone who has.
Is that an answer to the question you never asked?
"JB" <JB@discussions.microsoft.com> wrote in message
>I use a desktop at home and a laptop when I am on ...Finding the difference in days between two dates?
I was wondering if anyone could let me know how I can find the difference in
days between two date fields, would appreciate the help.
Just try this.
Enter two dates ( 01/08/2003 and 21/08/2003 )
In another cell subtract the two dates, but format that cell as "General".
If all is well, just must find a 20 in that cell.
Or is it too simple and not what you want ?
More Excel ? www.rosenkrantz.nl or firstname.lastname@example.org
"Damon" <email@example.com...Conditional Delete
I want to delete all cells that contain specific text. Any help would
I'd select that column and do
Then use the dropdown and choose custom
contains: (type in your value)
And delete the visible rows
Then data|Filter|autofilter (to remove the filter)
You may be able to chose the value you want from the dropdown, too--avoiding the
> I want to delete all cells that contain specific text. Any help would
> be appreciated!
...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?
...Creating a Report using two queries
I have created a chart report (bar chart) using one query. How can I create
another single chart showing the results of both queries. One will show the
bars of first query data and the other will show bars of the data from second
query together in a single chart report. Any suggestions?
This depends on how/if the two queries are related. I would expect the "X"
axis values might be related. If so, join the two queries into a single query
and graph the new query.
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www...Conditional Formatting in data table of a chart
I'm using Excel 2007, and trying to apply conditional formatting to the data
table of a line chart. I've tried creating the chart without the formatting
applied to cells with the original data (data source), and then applying the
format to the source; as well as creating the chart after applying the format
to the source. The data table in the chart will naturally update with
changed data since it is linked, but will not incorporate any style
formatting. Any thought? Thanks.
Data tables in charts are not particularly flexible. They do not, for
example, support conditional fo...JDBC-ODBC Bridge driver & ODBC driver are two different installati
desktop control panel settings, I have above URL (IMG) Settings
well, I need to run in my PC: Type 1 db driver Access / java[SE1.6] Desktop
Applications, what I need to install ?
I refer for Type 1, JDBC-ODBC Bridge driver.... Is it needed install and
Access 2003 ODBC driver (see URL) ...? How I understand if I already
installed it(?) since I do not know ? JDBC-ODBC Bridge driver & ODBC driver
are two different installations?
How to load driver in my program ? The below are correct for: Class.forName
& DriverManager.ge...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
...Two POP accounts with separate data files?
I'm using Outlook 2003. I've got two POP accounts, but I don't want mail
for both to be delivered to the same Data file (Personal Folder). Also,
I'd rather not use a rule to have mail from the second account simply
delivered to a different folder.
Is there a way to have two separate sets of "Personal Folders" and have one
account deliver to one and the second account deliver to a second?
Your name <firstname.lastname@example.org> wrote:
> I'm using Outlook 2003. I've got two POP accounts, but I don't want
> mail for both to be del...Range Vlookup
Hi Every One,
I hope you all are doing well, I required your help to resolve my
On single worksheet i have two different sheet name as 1.IP Range 2.
Start IP End IP Area
192.168.1.4 126.96.36.199 AG
10.15.33.10 10.18.56.254 EMEA
10.128.33.5 10.132.40.60 AP
Here it will Continued as well.
On Result sheet i required result like:
10.129.36.8 ...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
Is it possible to activate a Macro when a cell contains a certai
or anything simila
Heydilbert's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2874
View this thread: http://www.excelforum.com/showthread.php?threadid=48441
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
..."A configuration error.. caused the message to bounce between two servers"
For a few weeks now I've been trying to debug the connection between two
exchange 2k servers on my network. A new one and an old one. Users on
the old server couldn't send messages to users on the new one and vice
versa. I seem to have partially fixed the problem by Setting our two
internal MX records to have the same metric or weight or whatever you
want to call it. Now the origional server can send messages to the new
server. The new server however still cant send messages to the old
server. Anything you try to send in that direction
instantly gets the following message...Conditional formatting with linked value
I'm Paste Special/Linking formatted dates (dddd, mmmm dd, yyyy) and text
values in a calendar type list - linking to a copy of the calendar. When I
don't have any dates or values on specific lines, I want the copy calendar
to have blank lines. However, because I format the date I get a '0' in the
'blank' line. Or if I don't have a date on the line I get 'Sunday, January
0, 2000' in the copy calendar line. I've been trying to use the conditional
formatting (I use Excel 2007), using white as the color for the lines I
don't want to show, but can...Conditional format on data change
I've a 5-column table with financial data sorted by the field "payee".
As the table is extensive I would like to highlight the row(preferably)
or the cell at each data change in the payee field.
Can this be done?
I put some text in A10:A20 (a,a,a,b,c,c,c,........)
Used Format | Conditional Formatting with ->
Formula is $A10<> $A9
opened the Pattern tab and selected a fill colour
Now the rows in range where text in A changes are coloured green
Note it colours the first row (we could do something about this if
Bernar...Can you conditional format bar and pie charts in Excel 2000?
I don't know if this is possible in excel 2000 but I figured I woul
ask a quick question instead of continuing to spin my wheels on it. I
there anyway to automatically conditional format the color of a pie o
bar chart. In other words, I have a table of data that is going t
change from month to month. The table I am graphing looks like this:
Project Size Status
Project A $2m Green
Project B $.5m Green
Project C $1.2m Red
The number of projects and status' could change from month to month.
want to create a pie (or bar) charts that will auto format the...Combine Two Colums
Need help with the script below or maybe a better one. I need to combine the
items in Column A and Column B into one Column
The one below will combine the data into one column but it is backward, I
need the text in column B to be added after the text in column A
Thanks for any help Scott
For r = 2 To 100
Cells(r, "D").Value = "@website.com" & Cells(r, "D").Value
This is what I have
A B C D
1 Jimmy @website.com
2 Jimmy @website.c...Conditional Formatting 11-30-09
New to this....
I have a list of dollar amounts in column A. In column B, a 1 or 2 (yes or
no) depending on whether the bid was awarded or not. I need to create a
running total everytime a 1 is added to column B that equals the total of the
dollar amounts next to each "1".
will work provided the columns have nothing else in them but the data for
Otherwise, somethink like this with your actual ranges
BTW, this is not 'formatting' - that only changes the appearance of things.
You need a formula to cond...How To Do Vlookup But Have The #n/a Be 0
hi, does anyone know how to make vlookup instead of #n/a it appears a
Message posted from http://www.ExcelForum.com
Gord Dibben Excel MVP
On Wed, 18 Feb 2004 23:48:07 -0600, sokevin
>hi, does anyone know how to make vlookup instead of #n/a it appears a 0
>Message posted from http://www.ExcelForum.com/
thank moit :
Message posted from http://www...How many quarters between two dates?
Got a date question for you. I was wondering if anyone knew of
formula where I could do the following:
Withing a column, find the oldest date, and the most recent dat
listed. Then, find out how many quarters (three months) fell betwee
Anyone know?? Any help is of course greatly appreciated. Thanks!
Message posted from http://www.ExcelForum.com
"Rich9016 >" <<Rich9016.email@example.com> wrote in message
> Hi Everyone!