Hi,
I've seen spreadsheets before that when a user clicks on a cell that has a link to another worksheet (or even another workbook) [eg "=Sheet2!A1"], Excel will automatically jump to that cell. From memory this was not done through hyperlink.
Does anyone know how to do this?
Thanks.
Happy Friday everyone!
I'm using some code posted by Tom Urtis back in December for a drop down navigation menu. Unfortunately, in Excel 2007 it puts this drop down in the "Add-ins" tab so it is not always visible. Is it possible for me to add this drop down navigation menu to the Quick Access Toolbar?
This code is in a module:
Code:
Option Explicit
Private Sub ResetMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Sheet selector").Delete
Err.Clear
End Sub
Private Sub MakeCBO()
With Application
.ScreenUpdating = False
Run "ResetMenu"
Dim cboSheetz As CommandBarComboBox, ws As Worksheet
With .CommandBars("Worksheet Menu Bar")
Set cboSheetz = .Controls.Add(Type:=msoControlComboBox, befo =.Controls.Count)
End With
With cboSheetz
.Caption = "Sheet selector"
.OnAction = "mySheet"
End With
For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then cboSheetz.AddItem ws.Name
Next ws
cboSheetz.ListIndex = 1
.ScreenUpdating = True
End With
End Sub
Private Sub mySheet()
With CommandBars("Worksheet Menu Bar").Controls("Sheet selector")
Worksheets(.List(.ListIndex)).Activate
End With
End Sub
While this code is placed in the workbook object:
Code:
Private Sub Workbook_Open()
Run "ResetMenu"
Run "MakeCBO"
End Sub
Private Sub Workbook_Activate()
Run "ResetMenu"
Run "MakeCBO"
End Sub
Private Sub Workbook_Deactivate()
Run "ResetMenu"
End Sub
Thanks for looking and have a good weekend,
-Matt
I currently have a navigation menu with 9 options however these options are based on Range names that I have set up.
The concern is that if someone deletes the cell that contains the range name, obviously that option in the navigation won't work, nor would you want it to.
However if someone re-enters a cell that you would want linked up with the navigation there's a lot more action involved.
What i'm looking for is a macro that can be used through a button,
Idealy i'd like the macro to search through column "B" and look for key labels (ex: "Ground Floor", "1st Floor" "2nd Floor" etc.)
and have each button set up for it's respective search data (ex. Ground button searches only "Ground Floor")
what's the best way to accomplish this???
Good morning,
trying to find out what to do with my spreadsheet and the formulas in it.
If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.
But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.
I took the youtube tutorial, but even copying that formula did not work.
My table consist of 2 sets of 600 values each.
Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?
THANK YOU
BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]
When you are making a formula say
=A5 + A6+A7
when you are doing this in cell D423, it sometimes gets annoying to have to either type in A6 and A7 manually or to navigate to A6 and A7.
I know there is a key you can hold down after doing A5 that keeps you on A5 even after putting in the + sign, so that you don't have to navigate all the way back there.
I can't figure it out!
Does anyone know what this is?
Please help!!
Thanks,
Michael
I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:
Code:
=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])
except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?
hi i have writtern the following in a comman button
sub trythis()
dim x as range
set x = range("b1:b20")
for each x in x
if x.text = "June" then
x.copy
end if
next x
end sub
I have in the range names of the month repeated randomly. June appears 6 times in the list
By executing the above code the last cell containing "June" gets into copy mode (the cell is filled with marchings ants effect). Although the loop goes through many cells containing "June" only the last cell containing "June" shows this effect, how extend this effect to all the cells containing the text "June".
Hi guys,
Just trying to name a sheet using a string concatenated with today's date. I've also had problems trying to name it a variable, which I assume would be down to the seem problem as both throw the same unrecognised character error. I've tried using typecasting and without and it still doesn't work.
Code example:
Code:
workingWorksheet.Name = "Data asof " & Date
Also tried
Code:
workingWorksheet.Name = "Data asof " & CStr(Date)
Also with a varable
Code:
workingWorksheet.Name = nameS
Where name is a string variable.
None seem to work. Does anyone have any suggestions please.
Thanks
Tom
Hello,
I am constructing a make-shift database in excel but can't seem to find a way to get magic trick #213 to work.
What I am trying to do is create a dynamic filter via 16 criteria. I would like any results that match all criteria that I specify and then displays results in a separate table to the right.
Thank you so much for your help - I would attach my spreadsheet to this message if I knew how.
BR,
PR
Hello there.
First, and before all, I must admit that I am a kind a "dumb" for Excel, because I always were using it, only for better look's of simple tables, schedule's and similar. Although, I was all the time aware of great power's of this program, but newer tested it.
So, finally after few years of tempting, I decided to make a proper "order-list" for the shop in which I work. What I needed is to ,based on products and their prices, make a drop-down box, with list of the products, and after choosing one , proper price will be shown in next column. I find out how to do it with "Excel magic trick #5" on Youtube.
This is what i achieved:
http://i49.tinypic.com/dorzgn.jpg
However,
You see that in row 16, or line 2 in the order box ,we have #N/A below Cena (Price), Iznos PDV (amount of VAT), Cena sa PDV (Amount with VAT), and Iznos (Total amount). If we choose one of the product from drop-down box (now, as You can see none is chosen) and type number in Kolicina(Quantity) column, all this would be automatically changed into proper value.
But what if we have only one thing in order box, instead of 2 or all of 10 ? How to avoid those #N/A, and make instead to be blank cell, which is to be counted as 0, because of final calculation?
In this order-form we have only line 1 and 2 programed to have drop-down box in line Naziv robe (Name of the product). If I putted same drop-down boxes in rest 8 columns, #N/A would be shown in all programed cells bellow, like in line 2...
Kind regards from Serbia
