Replies

What am I missing? Your example is working fine the way it is. Are you trying to do this using VBA?

yoyoPHIL

Hey Buckshot;

I read your post a few days back and was intrigued with trying to figure out a solution. I like to think of Woody’s Lounge as a place to get the best “living brain teasers”. I admit that I have only recently starting learning VBA for Excel so most of my work is with formulas and the attached file only uses macros created by the macro recorder.
The spreadsheet I created stores the information of the donatees and then a pull-down box (name) selects the person and all pertinent information. This includes all donations, dates, special gifts, totals, etc..
The Data Entry button just links to the start of the data entry area (R69). Scroll down to enter all new data. The Re-Sort button is to use after you enter additional information. It will re-sort the data and look for any new names which will then be added to the pull down list.
The print area is set to only print one page which is the information for each person. There is room for 52 entries for the year. C8 counts how many donations are given and will turn red if more than 52 are found. Most of the formulas are array formulas so any editing will need to be finished with ctrl – shift – enter. I originally tried to set-up the sheet exactly like you posted but found problems with the layout so I did what I thought would be the most appropriate.
I don’t know if this is what you are looking for or if this file can be usefull but I hope it helps. If you wish more information on the file or assistance adjusting it, let me know.

yoyoPHIL

Hey Buckshot;

I read your post a few days back and was intrigued with trying to figure out a solution. I like to think of Woody’s Lounge as a place to get the best “living brain teasers”. I admit that I have only recently starting learning VBA for Excel so most of my work is with formulas and the attached file only uses macros created by the macro recorder.
The spreadsheet I created stores the information of the donatees and then a pull-down box (name) selects the person and all pertinent information. This includes all donations, dates, special gifts, totals, etc..
The Data Entry button just links to the start of the data entry area (R69). Scroll down to enter all new data. The Re-Sort button is to use after you enter additional information. It will re-sort the data and look for any new names which will then be added to the pull down list.
The print area is set to only print one page which is the information for each person. There is room for 52 entries for the year. C8 counts how many donations are given and will turn red if more than 52 are found. Most of the formulas are array formulas so any editing will need to be finished with ctrl – shift – enter. I originally tried to set-up the sheet exactly like you posted but found problems with the layout so I did what I thought would be the most appropriate.
I don’t know if this is what you are looking for or if this file can be usefull but I hope it helps. If you wish more information on the file or assistance adjusting it, let me know.

yoyoPHIL

Hows this?

Technically it is not looking for the asterix (*) but instead it counts all the entries for the specific date and then subtracts the total jobs done by the techs as calculated by your sumproduct formula.

Let me know if this is what you need.

yoyoPHIL

Hows this?

Technically it is not looking for the asterix (*) but instead it counts all the entries for the specific date and then subtracts the total jobs done by the techs as calculated by your sumproduct formula.

Let me know if this is what you need.

yoyoPHIL

I assume that your techs manually enter there name and the asterix (*) when there is a problem with the serial number. If this is not the case let me know how you want it to work. Your explantion didn’t explain it clearly.

I added conditional formatting that changes the serial number cell to a different color and the font to red when the tech name has a * at the end.
I also used your formulas on the summary sheet to count the number of names with *.

Let me know if this is what you are looking for.

yoyoPHIL

I assume that your techs manually enter there name and the asterix (*) when there is a problem with the serial number. If this is not the case let me know how you want it to work. Your explantion didn’t explain it clearly.

I added conditional formatting that changes the serial number cell to a different color and the font to red when the tech name has a * at the end.
I also used your formulas on the summary sheet to count the number of names with *.

Let me know if this is what you are looking for.

yoyoPHIL

Alex;

Here is an adjustment to your spreadsheet. I combined your formulas together and removed the redundant and uneeded columns. In G4 I placed

=IF(ISERROR(E4)=TRUE,SUMIF(‘#VALUE’!A:A,Sheet1!A4,’#VALUE’!E:E),E4)

and copied it down. Depending on how you get the results which are in column E (Route 1) you might be able to do this all in one column.

yoyoPHIL

Actually I don’t believe you need to have the =TRUE statement. Remove it if you want.

Alex;

Here is an adjustment to your spreadsheet. I combined your formulas together and removed the redundant and uneeded columns. In G4 I placed

=IF(ISERROR(E4)=TRUE,SUMIF(‘#VALUE’!A:A,Sheet1!A4,’#VALUE’!E:E),E4)

and copied it down. Depending on how you get the results which are in column E (Route 1) you might be able to do this all in one column.

yoyoPHIL

Actually I don’t believe you need to have the =TRUE statement. Remove it if you want.

Alex

Here is a stab at it. Sheet 1 has a formula in the E column which tries to run a simple calculation. Because some of the cell entries are text the result of the formula is #VALUE. I created this to mimic what you say is happening on your sheet. On the sheet called #Value the results from Sheet 1, column E are pulled into column A. In column B is a formula that looks at the results in A and if it does not find an error it keeps the result you have. Where it finds an error it places “sumif formula here”. Because I don’t understand your sumif formula, but you say it works, insert it into the formula in place of the “sumif formula here” statement adjusting for sheet names and ranges.

Hope this helps.

yoyoPHIL

Alex

Here is a stab at it. Sheet 1 has a formula in the E column which tries to run a simple calculation. Because some of the cell entries are text the result of the formula is #VALUE. I created this to mimic what you say is happening on your sheet. On the sheet called #Value the results from Sheet 1, column E are pulled into column A. In column B is a formula that looks at the results in A and if it does not find an error it keeps the result you have. Where it finds an error it places “sumif formula here”. Because I don’t understand your sumif formula, but you say it works, insert it into the formula in place of the “sumif formula here” statement adjusting for sheet names and ranges.

Hope this helps.

yoyoPHIL

ccj

When you say you have the numbers already in the fields do you mean JUST the numbers or do you have any apostophes or commas already entered. If your not sure, select a cell and look in your formula bar.
If you have just the number, say 12345 in cell A1, you should be able to do exactly what Cindi stated and the resulting formatting ‘#####’, should make your entry appear as ‘12345’,
Any apostrophes or commas already entered will screw up the formatting.

yoyoPHIL

ccj

When you say you have the numbers already in the fields do you mean JUST the numbers or do you have any apostophes or commas already entered. If your not sure, select a cell and look in your formula bar.
If you have just the number, say 12345 in cell A1, you should be able to do exactly what Cindi stated and the resulting formatting ‘#####’, should make your entry appear as ‘12345’,
Any apostrophes or commas already entered will screw up the formatting.

yoyoPHIL

Its a little hard to follow your set-up from the description. Could you send a smaller version of the file with any private information removed. It appears that it would be a simple formula but without an example I am having a difficult time picturing what you need.

yoyoPHIL

Its a little hard to follow your set-up from the description. Could you send a smaller version of the file with any private information removed. It appears that it would be a simple formula but without an example I am having a difficult time picturing what you need.

yoyoPHIL