Posts by PY_

Hello all,

I am trying to sort a range of cells A1:D3 in ascending order but while doing so, omit the blank cells and keep them at the end of the end of the range. Is something like this possible? I tried searching how to sort by ranges but all i have found is how to sort by columns or rows.

I attached an example of what i am trying to accomplish.

Re: Lookup X & Y And Return Z

Quote from AAE

=IF(NOT(ISBLANK(INDEX($A$2:$E$8,MATCH(A15,$A$2:$A$8,0),MATCH(B15,$A$2:$E$2,0)))),INDEX($A$2:$E$8,MATCH(A15,$A$2:$A$8,0),MATCH(B15,$A$2:$E$2,0)),"N/A")
This version will test for the a blank in the matching cell and return N/A. You might actually have a value that is zero and will not want to return N/A in such a case.

Thank you sir. I will look into which way works best for me.

Re: Copy Rows And Paste Into Columns

Quote from Yard

Copy, Paste Special, Transpose

Thank you again good sir. You have helped me twice in a row and saved me lots of time.

I am trying to save me a lot of copy and pasting. I have cells A1:A100+ with customer names listed out. I am trying to create a chart on a different page but on this particular chart, i need those customer names at the top of the columns going across the page instead of the rows. Is there an easy way to do this?

Re: Lookup X & Y And Return Z

Well, i was searching and searching last night for the answer and just gave up and decided to post my question. After posting, i found a similar formula not 5 minutes later.

Re: Lookup X & Y And Return Z

Quote from Yard

In your sheet:

=INDEX($A$2:$E$8,MATCH(A15,$A$2:$A$8,0),MATCH(B15,$A$2:$E$2,0))

Thank you. I guess i was on the right path after all.

Greetings all,

I am trying to set-up a formula that will look up two different values of a grid (x & y) and the result will be the intersection of the two values (z). If there is not a value at the intersecting cell i will have it display "n/a". I have attached an example of what i am trying to do but i have no idea how to set up the formula.

I forgot to mention that the index or chart above remains constant but the lookups do change.

I believe i may have figured it out. Here is what i came up with for cell C19. Should i have done anything different?

=INDEX($A$2:$E$8,MATCH(A19,$A$2:$A$8,0),MATCH(B19,$A$2:$E$2,0))

.

Re: Lookup Last Cell Containg "x" And Return "y"

Quote from tobeychris

Click on edit in your first post, then click go advanced, and you should see your misspelled title.

Thank you!

Re: Lookup Last Cell Containg "x" And Return "y"

Quote from RichardSchollar

Here's an option using your example:

=LOOKUP(2,1/(INDEX($B$3:$G$6,MATCH(A10,$A$3:$A$6,0),0)="Yes"),$B$2:$G$2)

Richard

Thank you Richard, that works great. I tried daniel.c's code but it kept returning "0" as the result.

I understand how to set up a normal VLOOKUP, but I am not sure how to do the following: I am trying to set up a sheet that will find the last cell of a row containing "yes" and display the value of the cell at the top of that particular "yes" column. Maybe VLOOKUP is not the answer? I have attached an example of what i am trying to do.

Re: Hyperlink List Of All Files In Folder

Well i figured out how to create hyper links. What im trying to do is create a custom menu of a few specific hyper links and then share that custom menu with other users on the same network.

Re: Create Custom Drop Down Menu

Well I figured out how to create a custom tool bar that has a drop down list of files that i want to link to. I set up all the links to each of them and now i just drop the menu down, pick the file i want, and it opens it. But, i can not figure out how i can load this tool bar on another machine without going through that whole process on each machine i want it on.

Re: Create Custom Drop Down Menu

The list would be hard coded paths. They never change and if they did, i would update the drop down list.

Re: Create Custom Drop Down Menu

Well the hyper links i have currently are just basic links i created by right clicking the cells, selecting 'create hyper link', and browsing to the files i wanted links to. All of these links are on one worksheet and i have all users just shortcutting to that file. I currently have about 10 worksheets i have links to. As far as whether I want an actual menu tool bar or drop down list, I am not sure which would be best. I wanted to set up which ever would be easier to install on multiple computers that all access the hyper links. I do like the actual menu tool bar (thats how my CAD is setup) but not sure the difficulty of creating it and adding it to other users.

I have a question about creating a custom drop down menu. In AutoCAD, I can create custom drop down menus that loads each time AutoCAD loads and i was wondering if excel have something similar. This menu would be added to the default menu list: "File_Edit_View_Insert_Format_Tools_Data...etc". I have searched online but i keep getting things about how to make drop down menus in cells but nothing about adding a main drop down menu.

What I want to do is create a menu with hyper links to all of the excel files we use quite often here at work.

I have already done this by just creating a sheet with a list of hyper links on it but would like to put it in a drop down menu instead if its possible.

Re: Automatically Copy Cell By Selecting It

Thanks so much for the help. although, i ran into a "too many line continuations" error.

.

Re: Automatically Copy Cell By Selecting It

I did what you said about the checking the “Microsoft Forms 2.0 Object Library” option and it now works. Thank you again sir.

What would i add/change on your code so that i can have the option of selecting between two different cells and copying the text of the individual cells?

I want to copy A3, paste it, then go back and copy B3 and past it. I need to set this up for multiple cells but once i get an idea of how to do more than one, i can just apply the pattern to multiple cells.

Re: Automatically Copy Cell By Selecting It

Thank you Bill for the corrected code. I tried to use it on a sample sheet but i get an error highlighting " MyDataObj As New DataObject".

I also attached an example of a book using the code i posted earlier. It is still copying the text for me. Any idea why it works on this one particular sheet?

.

Re: Automatically Copy Cell By Selecting It

I see what your saying. I just created a new sheet here at home and it clears it. I will look at my other sheet when i get to work tomorrow and try to figure out what its doing different.

Re: Automatically Copy Cell By Selecting It

Maybe i am confused about the clipboard question but if your referring to the windows clipboard, No. I click in a cell, window pops up, hit "OK", then i can go to MS word or whatever other program, paste, and the text that was in that cell is pasted.

BUT, i am currently doing it on a cell that only has text, it does NOT have a formula in it. Maybe thats the problem.