Thanks Roy, that is a nifty piece of coding and can use that in a variety of ways.
My problem is I have 30 sheets going to one chart sheet. What I would like to have is a return button (or something) on the chart sheet that once pressed goes back to the sheet I came from eg I am in sheet 22, press a button that says 'chart', I go to the chart sheet, when finished I press the return button I go back to Sheet 22.
It sounds as though this might be a bit difficult?
Again thanks,
GrahamB
Cleta,
Your very welcome, do not hesitate to ask if you have other difficulties, some of the other people on this forum are very very very smart,
Cheers,
Graham
WillR, thomach, johnccc
Many thanks for these proposals, I did not think of the formatting issues. The number I start with is a decimal eg 10.15 and not formatted for time. The reason is for easy data entry, the decimal point being on the key pad. (The data entry will be done by a KPO for about 500 times per day)
I suppose I could reconfigure the decimal key to be a colon key during this operation and revert when finished. What do you think?
GB
Afternnon All
In creating a spreadsheet I had the need to go from 'sheet1! or 'Sheet2!' or ....'Sheetx!' to a general chart sheet and back. Each time I do this I use a macro written for each sheet. That is I have 'x' macros to return from the chart sheet, if that makes sense.
Is there a simpler way of doing this? A way the VBA can id the sheet I have come from?
I am perplexed by this conundrum!
Ta muchly,
GB
Gidday Kalijwv,
There are a few ways to do this, the simplest is highlight cell C4, put the cursor on the little black dot on the bottom RH corner of the cell (a cross appears when cursor moves over that point) hold down your left mouse key and drag.
Check the formulii are what you want and go for it!
Reply to this post if further details are required,
Cheers from DU, GB
Gidday Tradertt,
I understand the problem, the process to select the maximum pricing is more long winded than a simple one line formula.
The way I have solved in the past is to
1. create a 'datbase' of purchased inventory,
2. sort the database by maximum price
3. when selling, a macro determines purchase price and number and removes it from the database.
This means your initial concept of horizontal selection will not work - unfortunately. Depending on how sophisticated your macros are you can have many products in the one database so it becomes and more complete inventory system. Have you considered Average Costing rather than FIFO or LIFO? I am sure this is acceptable in accounting principles.
Hope this helps.
GrahamB
It would appear your formula in cell E2 refers to itself and therefore making it react to itself ie a circular reference. I think the formula you are looking for is
=IF(C2-D2<1,0,C2-D2) where the '1' can be your reorder point eg =IF(C2-D2<6,0,C2-D2) would mean you reorder when 5 or less items remain in unused stock - hope this helps,
Graham
Evening All,
I need a UDF or formula that converts time from (hh:mm) to (hh) eg 10hours.15minutes = 10.25hours
I have creatred this UDF but is there a formula? [tc = total time, ft = finish time, st = start time]
Function tc(ft, st)
tc = (((ft - Int(ft)) * 100 + Int(ft) * 60) - ((st - Int(st)) * 100 + Int(st) * 60)) / 60
End Function
Thanks Muchly!
G
You fella's are amazing!, how do you know this stuff?
Forget the shillings, florines - go straight to sterling (One pound, one shilling - if my aging memory is correct)
I will put on my thinking cap and try and pose a very difficult problem - however, right now AFL bloopers is on the box -a must see!
Will, AFL is our local brand of violence we call footy. We have a bloke over here called Roca who has been rubbed out for 2 weeks because he half hit an opposing player with his elbow, With him gone hopefully the other team (read) Lions will cream the Maggies - (only a personal hope nothing political) - I am sure Pesky will have a view!
Will!, Pesky!
Nay to 2ps, 5ps - no way - the suggestion is worth a complete shilling! Make it 2-bob, if you can tell me what function counts a column position
(as an example, a column of 10 words starting at A5. I use vlookup and find my word in A10, what function tells me it is in the 6th row of the column?)
I reckon its hard but I am only a poor (read less than a shilling) sod!
ps Pesky, what is two bob worth anyway???
Chris, (Or should I say, Alfred E Newman?) - excellent, many thanks.
The one conundrum is that the array needs to have an additional column to identify what the column number is - does that make sense?
Is there a way the Vlookup can count the array (row) number?
Again thanks - the result is brilliant!
Graham
Recently I was creating a spreadsheet where I needed to use heaps of nested vlookups within a hlookups - I tried without success to create a UDF. -
My spreadsheet formula was
=HLOOKUP(E11,$M$36:$Q$49,VLOOKUP(F11,$M$36:$Q$49,2,FALSE),FALSE)
The UDF variables were hlu = E11, vlu = F11 & agmass = $M$36:$Q$49.
The UDF started 'Function LU(vlu, hlu, agmass)
