Posts by Analyst84

Re: Identify a number as being between two values

Alright guys I and a friend at work figured out how to do it in a round about way

I used to concatenate function and if functions to make the type of label and media into a part number. Also I used if functions to assign numbers to price breaks and I added that onto the part number as well. Then I used vlookup to match the part number the corresponding row and it populated the price. God do I love Excel and I love it even more when I bend it to my will!!!!

Sorry I got a little megolomanical there.....but thank you Richie. Your suggestion sparked the idea for me

Ok guys, sorry for being absent for so long. I have a real problem here and it's driving me nuts. As usual I am going to use an example to explain my problem.

Sheet One
Label - Black Thermal
Media - CD-R
Duplicates - 31
Per CD - ?

Sheet Two
CD-R Black Thermal 5-10 $4.50
CD-R Black Thermal 11-25 $4.25
CD-R Black Thermal 26-50 $4.10

Ok now I want Sheet One where is says Per CD to return the price that corresponds to the amount. You can change anything about the format, I can create a secondary sheet as the reference. I am having trouble tell Excel to identify that 31 is in fact between 26 and 50. I can get Excel to tell me that 31 is less than or equal to 50 or that it is greater or equal to 26 but not both. PLEASE HELP!!!

I have a workbook for storing questions and answers that our customer service people get. I have four seperate worksheets (all in the same workbook I am going to share it for them) and I want 2 columns displayed in the 'Report' worksheet at the end from all 4 other worksheets. The Question column and the answer column. Any ideas/questions?

Thanks you guys!!! Your code worked out great and it got the job done!!

SOLVED

what form should I write the month in?

Just type in September or is there a numerical code?

I am making a very simple reporting worksheet in a records workbook for leads. The records (in rows) have the first column (A column) containing the date the record was put in the spreadsheet. I just need a report on a seperate worksheet that will tell me how many records have a date with any given month. i.e. 50 in september, 67 in october etc etc etc

Thanks!

I have been using a spreadsheet to track the number of leads that have come in over the past few months. The first column holds the dates that the lead came in. We'll call the first worksheet "Jack's Sheet" then on the next worksheet (we'll call that one "Jack's Report")in the workbook I am creating a report to tally the number of leads per month. I have the dates transponded in the Jack's Report in the Excel 5 digit date form i.e. 38122 so all I need is to create a countif function that counts the number of times a date within a certain month appears in the column. Or if you have an easier way I would love to hear it.

Thanks for your help. I was out of the office all weekend so I didn't see any of your posts till this morning. I actually fixed the spreadsheet by having the forms retrieve from a row that was populated by a VLOOKUP. Here is how it works.
Sheet 1: This worksheet consists of the forms that display the data needed. In the top corner I have a spot where the user can punch in the record # they want.
Sheet 2: This just consists of one row working on a VLOOKUP. It matches the record number from sheet 1 to the records on sheet 3.
Sheet 3: This sheet consists of all the records that have to be called up. The record number is in the leftmost column so that it can be called up from the VLOOKUP.

If anyone sees problems that may arise in the future from setting it up the way I did, please respond. This spreadsheet doesn't go into full use for another two weeks so I still have modifying time. (oh the joys of setting your own deadlines

I am working on a two sheet workbook right now full of customer information, with each customer's info being kept in a seperate row. I need to create a form that can call up each of the records. I have already made a large amount of text boxes that display the first record I am wondering if there is anyway I can code buttons to make the linked cells in the text boxes go 1 number higher or 1 nunmber lower to call up subsequent records?

No actually I need them added together. If the one worksheet returns 30 another 40 and the last 10 I need it to add them to 80

Can you put multiple vlookups together and add them?

Here is the code I am trying to use
=VLOOKUP(A2,'04-01 to 04-30'!$C$2:$AW$600,3)+(VLOOKUP(A2,'05-01 to 05-31'!$C$2:$AW$600,3))
or
=VLOOKUP(A2,'04-01 to 04-30'!$C$2:$AW$600,3)+VLOOKUP(A2,'05-01 to 05-31'!$C$2:$AW$600,3)
neither of them wants to work

Alright can you perform a vlookup in one sheet within a workbook and have it grab values from a another worksheet. If you can please please give me n example equation.

Here is the situation. I'll have a workbook open on my machine. I'll click a hyperlink to a word doc and of course word opens and displays the file I want. But when I go back to Excel the workbook is hidden. I can't find the option I hope exists to turn this off. It's really just an inconvience but I hate it anyways. Any help would be greatly appreciated.

I believe if you use the entire path all the way back to the drive letter, the link will not change. I experienced similar problems and that seemed to fix it.

One of the end-users working on my spreadsheet has been complaining that sometimes she types into a cell and then all of a sudden Excel switches to another worksheet within the workbook. Is there a shortcut she is hitting on accident or might the explanation be more complicated?

btw I do like the new look of the forum

No I couldn't possibly do that. That makes FAR too much sense and is much much easier than my previous solution.

Thanx Thomach
That was the solution i needed

I have a spreadsheet with multiple worksheets that contain names and associates numbers as well as unique information to each page. My formula for auto-filling the names and id numbers would be ='Assc. Info'!B3 so that B3 on the other worksheets would be the same as B3 on the first. Well when the end-users sort the first worksheet the names change positions on the other worksheets but the other columns do not. Is there anyway to make the other columns "auto-sort" along with the names and id numbers.

For clarification: Let's say there are two worksheets named: Holla and Back
Holla contains 10 columns. The first three are First Name Last Name and Phone Number, the rest don't matter
Back contains 8 columns. The first three are first name, last name, and phone nunmber but instead of actual data being manunally typed in, columns A, B, and C all contain fomulas that link back to the same cell in the Holla worksheet. Example: B3 in Back has ='Holla'!B3 C15 in Back has ='Holla'!C15
Now when I sort Holla by Last Name, Back's A,B, and C columns change with Holla's but the other columns don't. Does anyone know how to make it sort with it or just not have the linked cells sort.

Ok I know there has to be a way but I don't know it
I have a 4000+ row list that has the columns First Name Last Name Address City State Zip Code. I sorted by Last Name and began removing the duplicate entries by hand. However there must have been a time that I clicked "Shift Cells Up" instead of "Entire Row" when I was deleteing. Is there an easier way to search for duplicate entries and remove them?

I am creating a spreadsheet that will allow the operators to enter in data quickly and then record it. I set it up using text boxes for entering the data and tabbing in between them would make it easier

How do I set up Excel to allow me to tab between the text boxes on my spreadsheet. I look in help and it said something about a tab set property but it isn't available to choose.