LinkBack URL
About LinkBacks
Hello,
I am trying to get the formulas in columns G and L to display the date that is shown in the appropriate cells in the relevant tabs, rather the cross that they currently do.
Any help is much appreciated.
Thanks,
John
Hello,
I am trying to get the formulas in columns G and L to display the date that is shown in the appropriate cells in the relevant tabs, rather the cross that they currently do.
Any help is much appreciated.
Thanks,
John
Last edited by JohnFex; 02-21-2012 at 10:33 AM. Reason: Solved
I suggest you use Evaluate Formula to step through what the INDIRECT function is giving you:
HTML Code:
I don't know if that is what you were expecting. The IF statement doesn't actually have a comparison so it evaluates to the content of cell B10 on the Dummy sheet which, although formatted as a date, is the positive number 3. Hence you get the TRUE part of the IF which is "x".
To what did you intend to compare it?
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
TMS,
Thank you for your reply, although I think I should have explained that I am a novice in my original post.
The key to this for me is there will be several hundred clients (and hence tabs) added on the sheet. The formula works very well for all the other cells in the sheet when I copy it into a new row (hence new client), but for the G and L columns I just want to show the date that is associated in that cell on the client tab.
My thinking was then that when I have a full spreadsheet I can just filter on the dates and see which comes next. I don't know if the if command is quite right for this, I though I could use a simple '=', but I don't know how to refer that to the client tab.
I hope that makes some sense, I realise I'm not great at explaining myself here either. I can also can tell that you're very experienced in excel and I wondered if you wouldn't mind looking at the other query I have open as I'm sure you could probably answer that in 30 seconds - it's http://www.excelforum.com/excel-gene...98#post2711498.
Many thanks,
John
G4: =INDEX(INDIRECT("'"&$A4&"'!B:B"),MATCH(G$1,INDIRECT("'"&$A4&"'!A:A"),0))
Copied to L4:
L4: =INDEX(INDIRECT("'"&$A4&"'!B:B"),MATCH(L$1,INDIRECT("'"&$A4&"'!A:A"),0))
In fact, you can copy the formula from E4 to W4.
I have also answered in the other thread.
Regards, TMS
TMS,
Many thanks for having another look. I have tried to put both of those formulas in and they tell me there is an error with the 'reference' part. Would you mind having another quick look.
Now trying to get my head around the other answer - very kind.
John
Home sheet: G4: =INDEX(INDIRECT("'"&$A4&"'!B:B"),MATCH(G$1,INDIRECT("'"&$A4&"'!A:A"),0)) ... returns 3
What reference part?
It works in the sample. What are you doing differently?
Regards, TMS
TMS,
Apologies if I'm doing something obvious and wrong, but thought I would attach the error message to negate my 1000 words.
Thanks,
John
That's not an error message. That's the function help explaining the structure of the function.
If I double click on MATCH, I see exactly the same thing.
Have you tried pressing Enter?
TMS,
Sorry for continued issues, but all I am doing is cutting and pasting it in, once I hit return then it won't let me go any further as it highlights 'Match' in the formula.
Could you send me the sheet you have with it entered correctly?
Thanks,
John
It could be your Regional Settings.
Try changing all commas to semicolons before you press Enter.
G4: =INDEX(INDIRECT("'"&$A4&"'!B:B");MATCH(G$1;INDIRECT("'"&$A4&"'!A:A");0))
Beautiful!!!
Can't thank you enough for that! Would you recommend anywhere I could read up on that so I don't have to bug you so much next time?
Thanks,
John
TMS,
Sorry to do this and the formula is working beautifully, but is there any way that I can tell it to leave the cell blank on the 'home' if there isn't anything in the cell on the 'tab'.
Last one I promise!
John
A bit laboured, but:
G4: =IF(INDEX(INDIRECT("'"&$A4&"'!B:B");MATCH(G$1;INDIRECT("'"&$A4&"'!A:A");0))="","",INDEX(INDIRECT("'"&$A4&"'!B:B");MATCH(G$1;INDIRECT("'"&$A4&"'!A:A");0)))
Regards, TMS
Even more beautiful - thanks!
John
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks