Replies

I have a list of items on a spreadsheet that has several dozen items on a drop down list, then one next to it with quantity.
What I’d like to do is once that pull down is chosen, to be able to total the cost of that item times the quantity in the next drop down into another cell.
Is that possible? if so, it will be extremely helpful.

Thanks!

See attached – Column A has a drop down list, Column B has a lookup to prices, Column C is where quantity is entered and Column D gives Quantity times price.

Regards,
Maria

I’m formatting a 30-40 page spreadsheet that contains text of varying length. Some of the entries push the character limit.

When I get ready to print the spreadsheet, I have to spend hours and hours hand tweaking the row height, doing a print preview, and then going back to the rows that cut off their text.

I’ve tried Auto Fit Row Height, but that just gets me in the ball park. Maybe it has to do with the various print drivers I use. I’ve picked through the help this morning looking for some way to add extra padding after the text (sorta like Words space above and space after, but it looks like this is not an Excel feature.

Anybody have a trick that works to make sure there is sufficient space around the text so that it doesn’t get cut off? I found a way to set an indent, but that only works in one axis (whichever origin is chosen). I need something that will set the horizontal spacing while my text is oriented normally (0 degrees).

The solution I use is this

=IF(AJ6=”ENTER RNL PREM”,REPT(CHAR(10),4),REPT(CHAR(10),2))

This formula checks that some specific text appears in cell AJ6 (change as needed) if it exists then Char(10) (Carriage return) is repeated 4 times to give the required row height, if another column has more characters than will fit in 4 rows, then the row height will change as needed.

The column with the above formula is not included in any print range as it is only used to determine row heights as needed.

Regards,
Maria

I believe you will have to reinstall all apps whether you go the Clean (Custom) install route or the In Place upgrade route. I believe the Clean (Custom) install gives a much cleaner OS because the In Place upgrade can bring problems from the old OS into the new OS. Check the Windows Upgrade Advisor and check in at the Windows Compatibility Center. If your present system already has problems, you might be playing with fire to do an In Place upgrade. Save yourself time and headaches and do a Clean (Custom) install and reinstall updated apps.

Consider this an opportune time to weed out those apps you do not use any more or upgrade those apps you need. Either way I believe you will need to reinstall all apps.

I upgraded my Vista Home Premium 32 bit to Windows 7 Home Premium 32 bit with the in-place upgrade on both my Laptop and on my Desktop with no issues. I did not have to reinstall any applications, except the ones that the upgrade advisor told me would be problems.

Regards,
Maria

1. Is there a way to restart the Internet connection short of a reboot?
Running Microsoft diagnostic from a right click on the Yellow ! Yield sign only says “No Internet Connection” DUH, NO KIDDIN

Sometimes when I switch on my Desktop PC, I do not have an internet connection. When I open Network and Sharing, I see that I am connected to Home and an Unidentified Network – these appear to clash and I don’t have a connection. To fix this I go into Device Manager and disable then re-enable the Network Card. This gets rid of the Unidentified Network and leaves me connected to Home which is what it should have been in the first place. I don’t know where this Unidentified network comes from but I wish it would go away forever.

I don’t seem to have this problem on my Laptop. Both computers were Win7 Home Premium upgrades from Vista Home Premium.

Perhaps disabling and re-enabling the NIC will work for you as well, I have not had to reboot to get my internet connection back.

Regards,
Maria

I just had a try at adding a calculated item – I found that you have to select cell B4 on the pivot table to do what you want. By selecting this first and then using the Pivot Table options to add a calculated item, this will give you a calculated item after the Old column and before the Grand Total Column showing the difference between New and Old.

Regards,
Maria

Len, In column C add the following formula =IF(AND(A1=”r”,B1=”vi”),1,0) copy it down as many rows as needed, then sum column C.

Regards,
Maria

When you say “the database could only be used properly”, exactly what happens to the others?

They got an error message (can’t remember exactly what it said) and the database either would not load or loaded only the first time and not subsequently.

There could be another issue here. When you open any database with Access 2007 (including the full version) No Code is allowed to run, until you create a Trusted Location.

I will get our network people to look at this, as I believe that this sort of thing is restricted functionality.

Until you do that, you see a warning that certain content has been disabled. Is this what is happening?

I think I did see that message at some stage.

John,

I had already seen this structure when I needed to change the shortcut properties on the network computer, but still could not get it to work for other users, who had only the run-time version of Access 2007 on their PC’s. What I need is to somehow get this working on the run-time or else get the network people to put the full version on their PC’s.

This has happened to me on several occasions, specially after using a particular worksheet when I switch calculation to Manual then back to automatic for subsequent worksheets. I think excel simply forgets that automatic calculation was switched back on. Very annoying behaviour.

Here is one example, for PostalAddress1.
There are a few situations to consider.

The simplest option would be to only do this in new records. This simplifies initial data entry, but leaves updates to be done manually.
Otherwise you need to think about…
If BusinessAddress1 is currently the same as PostalAddress1, then presumably you want it to update to the new value?
If you delete PostalAddress1, then do you want BusinessAddress1 deleted, if it is the same as what you have just deleted from Postaladdress1?

Code:

If me.NewRecord then
 me.BusinessAddress1 = me.PostalAddress1
end if

Thanks John,

That is exactly what I wanted – I have now added this to each of the Postal Address fields on the Data Entry Forms for new records. This will save having to type the same text twice when both addresses are the same. For existing records nothing should be changed automatically – all updates on existing records are done manually as needed.

Regards,
Maria

You can’t quite do this default values, because default values are present before you do any data entry.

There are three options I can think of.

[*]Use the After Update event of each of Postal Address fields, to set the value of the corresponding Business address field. Perhaps only if it is null, so you don’t lose anything different you have already typed it.[*]Have a command button that copies all the Postal Address fields into the Business Address fields in one go.[*]Have another Yes/No field “Use Postal Address” . Then any queries that want a Business Address check the value of this Yes/No field. If it is Yes, the query just returns the Postal Address as the Business Address.

I would like to use the After Update event on the Postal Addresses to set the Business Address Fields, but I am not sure of how to code this correctly so that existing text is not overwritten.

Using this method, would mean only minor changes to the existing database.

Try this =CONCATENATE(TEXT(A1,”dd/mm/yyyy”),” | “,B1)

Where the date is in cell A1 and the Test No is in cell B1

See attached. As you can see in column C the result is what you appear to want.

Thanks all for your suggestions, I have turned automatic update back on but told it to download and instal at a specific time each week – then it will only annoy me once a week.

Hi,

Does this do what you want? I added a third set showing the difference between the two years and then graphed the comparative data only.

Regards,
Maria

Lounger,

I have data validation using the following formula – =OFFSET(Planner!$C$8,0,0,COUNTA(Planner!$C:$C),1)

This works fine, however it appeas that there is no protection to prevent invalid data being entered into the cell as with normal data validation.

Am I doing something wrong or can the offset formula be altered to prevent this.

Any thoughts

When setting up data validation you have the option of setting an error message when invalid data is entered. See attached picture.

Regards,
Maria