Return Full Row
Apr 28, 2006
I would like to enable my user to search on any one of about 4 columns, when the result(s) are found I need to return the full row to a result form. I've had no problems getting the information from the form to the worksheet but I've run into a few problems now. I should be able to be able to return the rest of the row with the result if I can determine a way to return the row number. Anyway these are the things that I need (in a nut shell):
1. Return a row number
2. Pass values to a form who should be able to change according to the size of the array
3. Have an array of variable size, with variable headers.
View 9 Replies
ADVERTISEMENT
May 27, 2014
In the attached excel, I will be putting a string (Which will be a result of another formula) and there will be a table in column A & B(Length of the table will vary).
What I need is a formula to search entire column B and get the file names which is having that string and also the corresponding path
Table E6 to F9 contains the desired result (The result wont be more than 3 at any point of time
Search.xlsx
View 3 Replies
View Related
Apr 21, 2006
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View 7 Replies
View Related
Apr 25, 2009
I have found these three different ways to separate a full name in say column A row 1. But when i insert different cells into the equation i get lossed. What is the proper way to separate a collection of full names into first middle and last. Do you highlight the list and put in one general equation or do you have to repeat these steps for each individual name in each cell?
First Name
=LEFT(A1,SEARCH(" ",A1,1)-1)
Middle Name
=IF(ISERROR(SEARCH(" ", RIGHT(A1, LEN(A1) - _SEARCH(" ", A1, 1)), 1)) = TRUE, "", _LEFT(RIGHT(A1,LEN(A1) - SEARCH(" ", A1, 1)), _SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ", _A1, 1)), 1) - 1))
Last Name
=RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-IF(C1="",1,2))
View 9 Replies
View Related
Apr 30, 2009
I have weeks in format YY-WW (this week is 09-18) and wish to calculate the full date for the week commencing day for that week. '09-18 is in cell S8. To work out the date I currently have: =DATE(LEFT(S8,2),1,RIGHT(S8,2)*7-8)-WEEKDAY(DATE(LEFT(S8,2),1,3))
Which I thought was working fine, but turns out to be the WC date for 1909. I'll probably have to change the -8, that's not a problem, but I can't work out how to get it to 2009.
View 3 Replies
View Related
Aug 5, 2009
I have a macro which searches for the latest date down a column and across particular rows. Extract of the code is shown below:
View 11 Replies
View Related
Jan 19, 2010
how to make the full range variable...
View 9 Replies
View Related
May 12, 2006
This formula looks at a cell and replaces the full date with jst the month
and the year....but some cells are blank, in this case this formula sends
back "100"
I need to add something to this formula that when the cell is
blank...instead of putting "100" as it does now, I need it to put
"Unscheduled"...
Like "If 100, then "Unsdchedule"
View 10 Replies
View Related
Jan 13, 2012
I want only one sheet should in Full Screen Mode rest of the other sheets remain normal view(With Ribbon).
View 2 Replies
View Related
Apr 24, 2012
I am looking to get a user's email address.
On Sheet A (Foreseen Absences) I have users enter their own data.
On Sheet B (email addresses) I have users' full names and email addresses.
I have the following formula entered on Sheet A to get the user's email address: =vlookup(C173,'email addresses'!A:C,3,0)
This works fine for staff who enter their full names, but I've got a few who are entering things like: G. Thoms or Marie which results in a #NA error.
Is there an easy way to update this formula to account for these instances?
View 1 Replies
View Related
Aug 19, 2013
I have cells A11 to G250. In column E11 to E250 there are lots of duplicate product descriptions. Any duplicates in that column (E11 to E250) I want to remove that full row leaving just a single row (A to G) for that product removing the full row A to G for the duplicates.
View 2 Replies
View Related
May 29, 2005
This is a simple fine line crosshair which ALWAYS extends the full screen width and height. Excel users seem to be stuck with the little box cross. A full screen crosshair for Excel would allow one to instantly and easily see which row and column the cursor is on without clicking a cell or following from the cursor to the row/column with your finger on the screen. It may seem trivial, but once you have used a full screen crosshair, you just don't know what you've been missing.
View 9 Replies
View Related
Jul 12, 2006
I am creating a userform and would like to be able to automatically enter the name of the person using it. The way our network is set up is Username = 1st 5 letters of surname + 1st 2 letters of forename. Due to this i cannot use
Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function
as this returns the login name as opposed to the full name. I also cannot use
Function UserNameOffice() As String
UserNameOffice = Application.UserName
End Function
due to the fact that the office settings are defaulted to "Any Authorised User" unless manually updated by the user.
The users full name is displayed at the top of the start menu, but I have so far not been able to find a way to pull that into my form.
View 6 Replies
View Related
Jun 18, 2007
How do I make my Excel-document automatically display in full screen-mode when it is loaded?
View 10 Replies
View Related
Sep 29, 2008
Price - Bulk Price - Total price
And i need to calculate the total price (totalprice = (PRICE OR bulkPRICE * 2.5)
If Bulkprice contains a number i need to use that field but if it's empty i need to use Price.
View 14 Replies
View Related
Oct 13, 2009
If a cell in Column F = Closed Then, the entire row is filled green. If a cell in Column F = Open Then, the entire row is red with a strike through. I'm guessing this is simple, but I can only conditionally format on a cell-by-cell basis right now
View 2 Replies
View Related
Sep 1, 2013
My spreadsheet has a column formatted as 'currency' (because it's a column of prices).
I need to have accurate values in the fields, including pence, eg. '£24.32'.
However the vast majority are round numbers with no pence eg. '£34.00'
So I am looking for a way to not display the pence when their are none, eg. '£34' rather than '£34.00', but I don't want to round to the nearest pound.
View 2 Replies
View Related
May 22, 2014
I need to replace a full column with a certain word. It needs to start at a specific cell and needs to replace the full column until it hits an empty cell. I can't do a find and replace because the words in that column are all different words.
For example:
I need it to replace all of column F starting at F2 with a specific word and it needs to keep replacing until it hits an empty cell.
View 3 Replies
View Related
Feb 10, 2010
I have an excel workbook that I would like to
a) appear more app-like, and
b) show more data in the available space.
I do not want to go too elaborate by hiding (remembering and restoring) control bars and I reckon the View Full Screen is pretty much okay for my purposes.
I am currently invoking View Full Screen on Workbook_Activate but hitting the ESC key returns to normal view. I can already see my users hitting the ESC key for various reasons and ruining my master plan.
Is there are any way to prevent unintended ESC from Full-Screen or otherwise lock in the full-screen mode until revoked intentionally by macro (shortcut)?
View 12 Replies
View Related
Feb 10, 2010
I have an excel workbook that I would like to
a) appear more app-like, and
b) show more data in the available space.
I do not want to go too elaborate by hiding (remembering and restoring) control bars and I reckon the View Full Screen is pretty much okay for my purposes. I am currently invoking View Full Screen on Workbook_Activate but hitting the ESC key returns to normal view. I can already see my users hitting the ESC key for various reasons and ruining my master plan. Is there are any way to prevent unintended ESC from Full-Screen or otherwise lock in the full-screen mode until revoked intentionally by macro (shortcut)?
View 5 Replies
View Related
Oct 30, 2011
I have large workbook with various sheets. In my first worksheet I have some command buttons that call each sheet as needed. However, I would like each sheet to be displayed either as a web page or in a way that the user does not see all of the menus, bars and so on.
View 1 Replies
View Related
Aug 21, 2013
i have an excel workbook containing approx 20 worksheets. What I would like to do is make the workbook go full screen when the user opens the file and allow the user to save as into the same file path the workbook is stored when closed
View 2 Replies
View Related
Jan 10, 2014
I'm wondering what is the syntax to open a msoFileDialog box and select a file, resulting in setting a variable to the entire file path. If I use
msoFileDialogFilePicker
I'll only get the file name, without the path. I'd like to get the full path ending in the file name.
View 3 Replies
View Related
Jan 29, 2014
I am trying to create a spreadsheet the will populate/pull all tyre sizes that I have collected from different suppliers.
Tyre Size
165/80 R13
Size
Brand
Cost Price
Selling Price
Supplier
Cannt use Vlookup as it will list only one option.
View 4 Replies
View Related
Mar 18, 2014
I'm looking for away to convert the value of cells with a numbers that represents the days of the month as 1 - 31 to the full date.
As if the cell value is 3 convert to 3/3/2014 and so on. I tried changing the cell format which does covert it to 1/3/1900, then I experimented with DATEDIF but didn't get anywhere.
View 9 Replies
View Related
May 13, 2014
I am trying to create a system to organize my company products. Up until now we had no part numbering system so I created one. The part numbering systems goes as such
Brand Code.Product Code.Bike Code.Color
These are examples
S7R.SF.101.Black
S7R.SF.101.Blue
S7R.SF.101.Gold
[Code]....
What I would like is to create a formula to expand the part number back to its full length name using tables on other sheets. I have tables for the brand code, product code and bike code. What I would like to have it do is Lookup the first part of the code "S7R" on a column from the following page and return the data from the cell next to it. Then continue on to do the same for the second part "cs" and the third"101" and so forth.
Basically the top example above would expand to be
Strada7 Short Folding Aprilia RSV4 Black
S7R = Strada7
SF = Short Folding Levers
101 = Aprilia RSV4
Black = Black
View 2 Replies
View Related
Dec 4, 2008
This might be extremely easy, but why my Excel (nor Word for that matter) doesn't go to full screen anymore? I'm pretty sure that I haven't used any options, I do have a new graphics card, but all the other applications goes to full screen. I have restarted my computer. Basically I think that it's either some option, or then reinstalling Office.
View 9 Replies
View Related
Jul 22, 2009
I work for emergency services, and one of my colleagues has a GPS camera that snaps digital pictures and collects the coordinates where the image was shot. These coordinates are recorded in a database file readable in Excel. The emergency mapping software can load these points along with the images, but in order for the hotlinks to work (and thus be able to click on a point and load the image), I need a field in the database file that shows the full path name of the respective JPG image. As it is, the database file has a field (named ORIGINAL) that records just the filename of the image.
I need a field that contains the full pathname of the image. This is easily done in Excel, but I need an external script that will run this outside of Excel because I need to streamline the process. If dispatchers need to go into Excel and edit columns to make the data show up, they're not likely to bother.
I have a blank (not populated) column in the dbf (named INTERNAL) that I can use for the full pathname. What I want to do is begin with the text "C:/" and insert the text in the ORIGINAL column immediately following this. Is there a script, such as in a batch file, that will run this outside of Excel?
View 9 Replies
View Related
Aug 2, 2009
I seem to be stuck in "Full Screen" mode. When I go to the View menu, both "Full Screen" and "Normal" are highlighted. I can't seem to get out of Full Screen.
I am using Excel 2003, XP SP3 installed. This just started this am. I did download and install Microsoft patches this am. ???
I am able to save and use Excel but without normal view my formating buttons etc. are not available.
View 9 Replies
View Related
Dec 21, 2006
My workbooks have a column of state names that I need to replace the names with abbreviations. I am doing it manually with the Replace (Ctrl+H) right now, and it's very time-consuming since I'm doing it with numerous, large workbooks. I would be very grateful if there's a quicker solution. The state column is always the same (I). The states are United States and Canada, but I could edit if there's an existing solution from a different country, or modify a simliar work-around.
View 6 Replies
View Related
