i brought some work files home to practice some VBa on this weekend, and the indirect functions stopped working. i have no idea why this would be.

there's no macros or anything involved in this part.

i have a cell that says "=INDIRECT(CONCATENATE($N$3,"!",Template!$C23) )"

N3 is 'blah_blah.htm'. cell C23 in Template says $C$4 or whatever.

so basically i'm just referencing cell C4 inside an htm file. when i open the htm file with excel these fields should populate. this shouldn't have anything to do with the path etc as i've never specified the path, it simply just checks that the file is open.

i notice that when opening it at home in the title bar it just says 'blah_blah' and not 'blah_blah.htm'. no idea if that has anything to do with it, but changing N3 to 'blah_blah' obv doesn't work.

Assuming this works as expected at your work, my first guess would be that the you have them in different folders than you have at work, try changing N3 to the full path (i.e. c:\users\documents\blah_blah.htm)

Edit: Does it work without the formula (i.e. just putting ='blah_blah.html'!$C$4 into a cell)

i've simply never used the path at all. it should simply just check if a file by that name is open in excel

will try with the path now and also what you said

I just tested it in my environment using: =INDIRECT(CONCATENATE($N$3,"!",Template!C23) ) both files on the desktop. It only works if you open blah_blah.htm from within the other file (by going file > open) just right clicking on blah_blah.htm and going open with excel makes the formula fail.

I suspect this is because of the loose referencing used its the only way for excel to recognise it, the full path would be something like:
'c:\users\[blah_blah.htm]Sheet1'!C4

mmmmmmm yes, thank you!

it didn't work on excel 2010 on my desktop either. super weird as i know for a fact it works the other way at work.

anyway that's an enormous help, cheers.

I have a feeling this will be a simple one, but it's stumped me...

I own a restaurant, and keep a list of Frequent Diners, and send out periodic specials, coupons...etc. One of my columns is Birthdate, and as I add new members to the sheet, I then sort by Birthdate.

Problem is, I recently discovered that when I input someone's Birthday as 5/16, the cell entry is 5/16/2011 (at least it was until 9 days ago). Now, if I input someone's Birthday as 5/16, it's saved as 5/16/2012. When I go to sort my data by Birthdate, I now have what essentially is two lists, one from 2011, and one from 2012.

Is there any quick way to remedy this? I only need the month and day, I don't WANT the system to put a year in at all.

Thanks!

you can add another column and use

=MONTH(A1)&"/"&DAY(A1)

This might be a simple question but i cant seem to figure it out.
Im making a sheet where im keeping track of my live poker results.
So i got this chart, where i fill in my sessions (date, stakes, BI, Cashout, time played) and it calculates all my profits/losses and winrates etc etc.

My problem is that the size of the file is 16Mb.. ive checked for circular references, copied the chart to a new sheet. but nothing seems to help..

Any ideas, or should i just start over?

Thanks

Did you select entire columns or rows and apply borders or cell shading?
Do you have multiple sheets? If so, save a copy of the file and delete one sheet at a time. You have to save after each delete and then check the file size. Narrow down where the problem is.

copy/paste the data into a new spreadsheet, selecting only the cells you need rather than full rows or columns.

Simple question from an excel fish:

I'm trying to add a list of entry from one row into other areas of a spreadsheet. The issue I have that then when a cell doesn't have an entry it shows "0" where it is set to be copied.

Example:
A3 = Apple
I have "=A3" in cell H72
If Apple is entered in A3, H72 will also show Apple
If Apple is deleted in A3, H72 will then show "0"

How can I make it so if Apple is deleted in A3, H72 will be blank (until something is entered into A3 once again)?

=IF(A3="","",A3)

or

=IF(ISBLANK(A3),"",A3)

I have two spreadsheets which are seperate due to the amount of data on them. I want to copy and paste data from one spreadsheet to another spreadsheet without having to manually enter it. I'm guessing the best way to do this is a macro?

I will be doing this for many different cells accross the two spreadsheets.

What is the best way to do this and how do I do it?

I also created a report which needs a unique number preferably each time I open the spreadsheet and in sequence, approximately 6-8 digits long. How would I do this? I've searched online a lot and cannot find anything that helps.

Thanks in advance!

Thanks ZOMG.

One more question - What formula would I use to copy a cell into another cell only if a different cell was blank?

Example:
I'd like to copy A2 to B2 ONLY if A1 was blank. If there is an entry in A1 then leave B2 blank.

Thanks again!

in B2 you can put

=if(A1="",A2,"")

if it helps the way the if statement works in english is like:
if A1 = "" then show A2 else show ""

This is pretty easy to do with a macro it depends if there is a automatic way to work out which cells you are copying or you manually do it. Also where are you copying them too in the sheet? Also how often are you copying it? is it a one time thing or do you need a button that does it or do you want to be able to click on a cell/range and automatically copy it to the other work book

the basic VBA assuming both workbooks are open would be something like

Assuming your unique sequential number is in A1 you can do something like

which will check if A1 is empty, if it is it will create a number between 999999 and 99999999 (i realize this is not a truly random 6-8 digit number but you can mess with the randbetween parameters if you feel like it)

Excel VBA question:

I have a macro in one workbook that creates a new invisible instance of Excel, opens a second workbook there, and reads/writes to it. The code has worked running on a lot of different computers in both Excel 03 and 07. Someone tried running it on another computer yesterday and got a "Workbooks.Open method fails" error. The code execution stopped and hitting debug highlights the Workbooks.Open line. What makes it really weird is that the workbook is actually opening properly. I had him try running the code again after putting in On Error Resume Next into the sub and the workbook opened, it could be referenced and read using the object variable I set it to. But eventually Excel completely crashes so that's not a very good fix (not that I would be happy with a fix like that anyway, even if it did work). wtf?

That is strange, can you paste the full workbooks.open line?

Obvious questions first:
Is the version of excel different in this case?
Does it work if you don't set application.visible = false (assuming thats what you mean by invisible?)
Are both the workbooks in the same folder? if not are the paths set differently on this computer?
Do you check if the workbook is open first before running workbook open?
Have you tried not creating a new instance of excel and just running it from the same one? Is there a reason you don't do this anyway (aside from not being able to set it to invisible)

Sounds like it would be impossible for me to reproduce, if you get really stuck i'm happy to jump on teamviewer and have a look?

This only partly worked:

If A1 is blank, B2 will show "0" (not copy what is in A2).

(It will correctly leave B2 blank is A1 is not blank)

I believe the issue was when A1 and A2 were both blank, this should fix:

=IF(A1="",IF(A2="","",A2),"")

I don't have a copy of the workbook here but it uses the Workbooks.Open method on a module level object variable set to an Excel app in the previous lines with the filename set to a cell value. So it's something like

Module.Variable.Application.Workbooks.Open Filename:=Range("A1").value

I don't think there's a problem with the syntax since it works on every other computer I've run it on.

Nope, at least I know it's Excel 2007. As far as any differences in add-ins or references I have no idea. It did have an issue at first with a missing reference (REFEDIT.DLL) because the offending computer had an older version of that file which we fixed by copying a new version to the appropriate directory.

Same problem with that line commented out.

Different folders but the cell reference contains the full path to the workbook. There's also a check to see if the file exists and if not opens a prompt to find it so we put in a bad reference intentionally so that the cell reference could be set by actually navigating to the file and selecting it. The Workbooks.Open will never run unless it finds a file, and it does actually open the workbook, it just throws an error along with it.

Yes. Checked for any instances of EXCEL.EXE running in the task manager, opened Excel without opening a workbook to clear out that list of files that Excel says it can recover for you after a crash, closed Excel, rechecked task manager and started from there.

No, I wanted to try that at one point but I'm not the one physically troubleshooting this. It's on a computer 500 miles away with no internet access. I've spent 3 hours on the phone with a guy who maybe knows how to write a few formulas guiding him around the visual basic editor. So I wouldn't just be able to tell him to try opening the workbook in the first instance of Excel, I'd have to tell him where and what to type. I'd like to know if that works just because I'm curious but I wouldn't want to use it as a solution because I want to keep it invisible.

Sounds like it would be impossible for me to reproduce, if you get really stuck i'm happy to jump on teamviewer and have a look?[/QUOTE]

Yeah I can't reproduce it either. Teamviewer wouldn't work (see above) but thanks for the offer.

Here's what I would try and if this fails then i'm not sure if I can help without seeing the full set up.

1. Convert the VBA so it doesn't use a new instance of Excel and just opens it using workbook.open
- note if it still fails then see above

2. If it fixes the problem there are a couple of ways you can hide a window without creating a new instance.
a.
b. I believe you can do something like
Sorry I can't be of more help, let me know how it goes

I use Windows Task Scheduler to open a workbook each day. It always opens in a new instance of Excel. Is there a parameter I can use to have it open in the current Excel instance (if there is one)?

I am grunching but used the search tool to try and find "hourly" or "rate" in this thread with no success. I am trying to figure out how to do an hourly rate calculation for my poker spreadsheet. I have "hours played" as a calculated field (A2+(A1>A2)-A1) and I have the "total" (won/lost) as a simple calculated field. Any suggestions?

Total / hours played
It may format it as time by default. Change it to a number format.

What am I missing?

Though your 'hours played' formula looks odd - "A1>A2" returns true or false.