I have 2 spreadsheets. One has one type of info and another has another type of info for the same items.
For example:
1st spreadsheet has: server name, serial number, model.
2nd spreadsheet has: amount of memory for each server, number of CPUs, etc.
Note that some servers are missing from one or another sheet. So all info needs to be combined.
What is the easiest way to combine all info in a single spreadsheet?
I need to take in third sheet all data which are missing in second sheet from first sheet, for example:
IDName LastName
1John Doe
2Jany Tine
3Paull Marphy
4East Doe
5West Marlyn
6ThirstanWist
7SecondEast
8ExampleMiddle
9Doe Tryer
10MarlynEarly
Sheet 1
IDName LastName
1John Doe
4East Doe
9Doe Tryer
10MarlynEarly
Sheet 2
And in Sheet 3 to get result
2Jany Tine
3Paull Marphy
5West Marlyn
6ThirstanWist
7SecondEast
8ExampleMiddle
Sheet 3
I am pulling my hair out trying to figure this out. Any help at all is greatly appreciated. I created a spreadsheet for a friend to track his profit/loss for his business; he is not very computer literate so I created separate tabs for each month. I have no idea what he did but the horizontal scroll bar and all of the tabs are not visible, he is using excel 2000 on windows XP. Unfortunately we are not in the same area so I can’t go look at his screen. When he emailed the file back to me and I opened it, it looks fine. Also when he opens any other file it does the same thing. I have gone through all of the obvious causes, everything is correct in the tools, options.
View 8 Replies View RelatedMy main sheet has the results of a competition in rows 1-100. 75% of the results won't count for the jumpoff ( its a horsey competition) and I want to automatically copy the successful details to another sheet. I've worked out how to to omit the unsuccessful results by using an IF statement ( IF cellx is > x then copy ) but I still end up with 100 rows - with 25 results and 75 blank rows. How can I copy but compact it down to 25 rows.
As a second question I need to sort the results. I know how to do a manual sort & I even worked out a macro so one keystroke can do the sort. It woruld be better if the sort would be automatic whenever there is a change in the scores.
I have a sheet which contain an horizontal split (Option Windows/Split). I have selected the row 16 to split the sheet so that I can have a header when I scroll down. However, when I print this data sheet, the first row, at the top of the screen, is missing. In the preview and on the printed sheets, the first row is missing on the next printed pages. In the first page, all is ok, but not on the next ones. Does someone have an idea why Excel is behaving like that?
View 5 Replies View RelatedI have a summary file in which I capture data from multiple sheets in one existing file (targetfile.xls).
One month might contain sheets that do not exist next month. When updating (edit) links, Excel reports an error (invalid external reference) on the first missing sheet, and does not continue checking/updating links for the rest of the document.
I tried =IF(ISERROR( SUM('[targetfile.xls]sheet1'!$M:$M)),0,SUM('[[targetfile.xls]sheet1'!$M:$M)),
I have two columns which i want to compare, they contain text data such as A123.
what I'd like is if its in column A and not in Column B then add to bottom of column A.
Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.
How i could this one in formula or macro, first my 1st sheet is just as show below (maybe use for a template)
1st sheet (full list)
fruits
china
orange
0
0
0
0
fruits
china
apple
[Code]...
then 2nd sheet, report i get which something like this :
fruits
china
orange
1
25
1
500
fruits
jiapan
kiwi
2
[Code]...
then i want result like this on 3rd sheet :
fruits
china
orange
1
25
1
599
[Code]....
so basically i want add missing row on sheet 2 from sheet 1
I am using office 2010.
On occasion I cut a table from a website and paste into Excel. If/when that data is subsequently deleted from the sheet the gridlines no longer show. I've tried turning the gridlines on and off to know affect. I've tried setting and clearing borders, but that made no different to the gridlines either. Even if I highlight the entire sheet and delete it makes no difference. I can't think of anything else to try.
View 2 Replies View RelatedI have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:Â
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:Â
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
I have done the two obvious checks regarding my missing Status Bar, both in View and Options and still the Status Bar refuses to show itself. I have shut down Excel with the options unticked hoping that on restarting and ticking them the bar will re-emerge but still no joy and also done vice-versa. how to get the Status Bar to reappear?
View 5 Replies View RelatedI have attached a screen shot of vlookup formula. Whilst selecting a range a little tooltip comes up showing how many rows and columns have been selected (6R x 2C) ...
... at least that's what used to happen. Now this handy tool tip does not show on my PC - and I have no idea how I turned it off, or more importantly how I can turn it back on again.
I am running Excel 2002 SP3 on Windows XP.
I have received a spreadsheet by email and when I opened it the formulas all had errors. I did a little investagating and found that when I use the viewer to open it there is an additional tab that is missing when I open it in Excel. I did check for hidden tabs but then realized that the formula would have still worked even if the tab was hidden, in this case it is just missing.
View 2 Replies View RelatedThe title bar is missing for one 2002 file. This is the same bar that has the minimize/maximize button in the right corner.
My mind is drawing a blank for a fix. I've tried the *view, tool bar, customize* and can't find the missing bar.
I have a hyperlink in my excel file which refers to cell in a separate excel file (which no longer exists). I therefore would like to remove this 'dead' link as it keeps trying to update it when I open the file.
I don't remember where this hyperlink is in this huge multi-sheet file so I can remove it! How do I have excel show me all hyperlinks and the option to remove them?
I am facing a problem importing Excel data into a finicky graphics program. This program can't accept strings and numbers in the same column. Hence, strings such as #NA, or Excel's standard "" can't be used where there are missing values, as the entire column is then ignored.
But, I would like to provision for future data below the rows that currently exist.
Let's say I am trying to read data from Sheet1 of a workbook. The rows in Sheet1 contain formulas referencing other sheets (e.g. Sheet2!C1-Sheet2!C2). So, if Sheet2 doesn't have data in a certain row, Excel would show 0 in the formula on Sheet1. That would mess up my graph. If I use any other string or "" using a If statement for missing data, then the graphics program doesn't work.
Is there any way say, using dynamic ranges or similar, to populate blank cells in Sheet1 as data arrive in Sheet2, without a pre-existing formula being present in these Sheet1 cells? I can imagine that VBA macros will easily do this (e.g. the thread on Autofill below). But, is this possible without having to trigger a macro as and when additional data arrive?
number and number 2 is what i have.. I would like it similar to the right side of this sheet where the missing ones are at the bottom of E
sheet: ...
i have a workbook with missing dates and would like excel to insert any missing dates and add 0's into that missing dates if there is no data...
for example....
i have a bunch ofdaily rates back from 2005. sometimes ill have one or even 2 or 3 missing rates in a row. when there is a blank rate, i just want excel to calculate the average of the date below and the date after. right now, ive just been going manually to each missing date's rate and calculating the average.
View 9 Replies View RelatedI have created a 4 page chart in landscape with text in each of the 4 columns in the chart.
When I looked at it in print preview the chart did not fill the page and so to widen the last 2 columns I dragged them over to the right hand margin. I am still definitely within the right hand dotted line showing the margin limit.
The problem is that I cannot now put a border line down the extreme right hand column. Every time I click on any of the border instructions in the Font tab nothin appears on the right hand side of the box.
I want to write code that will allow me to show the Missing Names in the Old sheet that aren't in the New Sheet.
I want the code to go along these lines:
Dim a As Long
a = 2
Dim o As Long
Dim p As Long
Dim last_row14 As Long
Dim last_row24 As Long
Can anyone answer this one for me - why does this piece of code run on some machines but not on others - I suspect it is something to do with a missing library but I have no idea which one.
When the code fails it highlights the word " Date" inside the brackets
TrainingDateBox.Value = FormatDateTime(Date, vbLongDate)
Is there an alternative code?
I have been working on a pretty extensive Excel Workbook for quite some time, finished it today after testing it multiple times, everything was working fine on my PC. But after I pasted the document on one of the network drives at work, I started getting compile errors. I am getting these errors on basic functions that I used in my code such as Date, and the Left and Right functions. I figure I must be missing some sort of reference...the error I am getting is (Compile Error: Can't find project or library).
I can't understand why everything worked fine on my computer and as soon as I paste it on the network, the application blows up for my coworkers.
For some reason colors will not show on any EXcel doc. When I type colored text in a cell, it shows the color until I press enter, then it goes black. Cell fill colors do not show at all.
However when I send the doc to another computer the colors wil show.
we are currently upgrading from Win2000 and Office Pro '97 to WinXP sp2 and Office Pro 2003. I am testing our applications for compatibility and am discovering many that fall over because mscomct2.ocx is missing. Does it normally come as standard in Office 2003 or not? If not, when did it stop being standard?
View 2 Replies View RelatedI have a formula that looks like this:
=HLOOKUP('Output'!$B$3,'Input'!$B$1:$P$300,'Input'!$A3+1,FALSE)
It is working very fine, when the cell has a value, but when it doesn't - it will return 0. So my question is: is there any way to make it return certain value or word like "No value" instead of zero?
I am trying to import data from web to excel. I posted the link below to the website I am attempting to import from. The only part I need is the statistics. However, I cannot get it come thru by getting external data from web.
The goal is to great an auto updating spreadsheet with multiple sheets so copy and paste is not feasible.
[URL]
I have a list of records that for which I cannot find values for. Essentially I have a dataset that I need to match to other information but for some records this 'other information' is missing.
1. I have a list of records with missing information
2. I have a folder with a large amount of sub-directories
3. I have many DBF files inside of these sub-directories with X columns and x rows
4. Somewhere within some row of some column is a value that links to my list of records
5. I need to search for my value in, say, A2 and return the file name and directory in which it lies
6. It can be text or number
is this clear?
I have some code to generate hyprlinks from a folder
View 13 Replies View Relatedthis is exactly what i am looking for also, can you please explain how i can use that code in 2007? I have attached a list of the numbers i'm working with, please can you have a look for me?
I've tried entering it ion visual basic but i keep getting:
COMPILE ERROR
INVALID OUTSIDE PROCEDURE