#### Getting Cell Value from the Concatenate formula

I built a concatenate formula that returns the following result: =Jul!
\$D27

I am looking for the cell contents of Sheet: July   Column D   Row 27.
I tried to use offset, but I am stumped. Can I add something to the
front of the concatenate to not only build the reference to the cell,
but also return the value instead of the =Jul!\$D27 ?

Thanks
John
 0
flem123 (3)
7/26/2009 3:12:12 PM
excel 39879 articles. 2 followers.

1 Replies
453 Views

Similar Articles

[PageSpeed] 19

=indirect(yourformulahere)

or July???).

Depending on the name of the worksheet, you may need to have a string that looks
like:

'Sheet 99'!d27

=indirect("'" & a1 & "'!d27")
if A1 contained the sheet name.

If you don't need the apostrophes, it won't hurt.

Johnny wrote:
>
> I built a concatenate formula that returns the following result: =Jul!
> \$D27
>
> I am looking for the cell contents of Sheet: July   Column D   Row 27.
> I tried to use offset, but I am stumped. Can I add something to the
> front of the concatenate to not only build the reference to the cell,
> but also return the value instead of the =Jul!\$D27 ?
>
> Thanks
> John

--

Dave Peterson
 0
petersod (12004)
7/26/2009 3:29:39 PM

Similar Artilces:

Help with formula #31
I'm sure there is a way to modify this formula but have no idea.I'd like for it to do this: =IF(A30="","",SUM(C3:C25)*ROUNDUP(Worksheet2!G3/G1,0)) which is fine. But if Worksheet2!G3 has more than whats in G1 then MINUS Sum(C3:C12) to the above formula. Example: Worksheet2!G3 has the total Qty. Sheet1 has a number in it (25) For every 25 this formula is dividing Worksheet2!G3 by 25. I want it to divide only once, then Do formula Minus Sum(C3:C12) as many times as G1 will go into it. Thanks in advance!!! ...

Getting a "Reserved Error 3099"
Hi I've developed an application and deployed in an ACCDR format. First of all this format generates a lot of errors in Vista/Windows 7 and it works fine in XP. I have been forced to release the ACCDB to Vista/W7 users. But now both the ACCDB and ACCDR versions shows this error during a valid and legitimate call "database.execute(sql string)". That sql command is correct and works fine, but on that machine I see "Error Number 3000: Reserved Error (-3099); there is no message for this error" This is really annoying since I don't know what's wrong a...

How do I add row total when cell contains both number and letter .
I have a timesheet spreadsheet. Cell contents include hours and coding. Example: 8 (8 hours regular pay-no letter) or 8V (8 hours vacation pay). I want to have a total at the end but my formula will only read the cells that don't have a letter after the number. If the text value is always "V" you could use something like =SUM(IF(RIGHT(A18:A21,1)="V",VALUE(LEFT(A18:A21,LEN(A18:A21)-1)),A18:A21)) entered as an array formula (control-shift -return) If there are other codes than just V, it gets a little more complex. if the coding is never more than just one charac...

Summing values
Hi, I have several columns of data in the format "nnt" where n= numeric value, t=text. I need to sum up the numeric values, based on the text value. Is there any way I can do this? Thanks for the help. You need to give us more information, is it always 2 digits and one text character or can there be more than one text character? What would the condition be. For a test assume there is always one text character and always at the end 12t 1n 24t 9t 16n assume you want to sum all the numeric values that ends with the text character t =SUMPRODUCT(--(RIGHT(A1:A5)="t"...

Money 2006 Portfolio two different values for Annualized Return
I have the test drive version of Money 2006. In the portfolio manager, I have the Annualize Return column visible. This provides values for each holding, each account, and the entire portfolio. At the bottom of the portfolio manager, I have selected Annualized Return as one of the three to display. My totals line my portfolio displays an annualized return of 7.5%, on the bottom, it displays 10%. I bounced around changing the bottom line to display values such Cost Basis, Market Value, and Total Returns and these all match the line labeled as Total Portfolio Value (\$). I believe t...

How do I indicate a empty or blank cell in an IF function?
I am trying to do a simple IF function but don't know how to indicate if a cell is empty or blank then I want a certain text value returned. What I am trying is: =IF('Sheet 1'!A15=IS NULL,"NO","YES") What I want is if the cell indicated on an other sheet is empty or blank then the word NO to appear in the formula cell and if it does have text then YES to appear. How can I do this? Hope I made myself clear enough. I couldn't figure out how to ask help the right way to get the correct answer. Thanks for all of your help! Never Mind. I seem to ha...

Get outlook icon back on desktop. How?
I accidentally deleted the Outlook icon (office 2000) on my desktop of a 2003 server. How do I get it back? Apparently it's not just a copy or shortcut of outlook.exe. I already tried that. Dave Fixed it. I just did a "detect and repair" with "recreate icons" checked. ------------------------------------------- "Dave Niemeyer" <nospamdniemeye@hotmail.com> wrote in message news:OWYKMCpeEHA.2804@TK2MSFTNGP11.phx.gbl... > I accidentally deleted the Outlook icon (office 2000) on my desktop of a > 2003 server. How do I get it back? Apparentl...

Excel 2003
When I click on the end of a formula in a cell, I usually get all the associated cells highlighted with different coloured borders. I have one workbook with several sheets where this no longer happens on just one sheet. Is there some option I need to turn on in the sheet to make the coloured cell borders appear? Or might it be something to do with very recently using automaic update to bring my Office 2003 up to date? Any assistance much appreciated. Tom Walker You should always get *exactly* the same actions if you would click in the formula bar. As far as having the cell itself h...

WSDL generates with wrong "minOccurs" values
Using the XSD.exe tool, I created a number of classes from my XSD file. When I generate the WSDL for my web service, the schema does not have the proper Occurance constraints for the attributes. In this example, the MessageID element should always occur (minOccur=1, maxOccur=1), but the WSDL generates minOccur=0. I can't pinpoint why this is happening and I can't find a may to force this in the code either. Anyone have any insight? This is happening with a large number of elements, I just provide one example below. Code of the schema, classes, and WSDL excerpts below. Thanks. //...

Get error message when press Test account setting
I just set up a new POP account on my Outlook and i get this message everytime i press the Test Account settings button. Send test email message: Unable to send test Message. Please verify the E-mail Address field. I still can send and receiev mail but this error is really bugging me. ...

GET FREE QUOTES from professional moving companies in your area

Color rows based on date in a cell?
Programming question? Hopefully so. Excel novice here I would like to color a row yellow at a certain date, then red at another. This is for a project and the yellow and red correspond to due dates. Say at 12/11/2009 the row is yellow. At 12/19/2009 it is red. I would also like to color a row (some color) when a 100% is reached in the "completed %) cell. Help is appreciated! Ignore, as this is now in General Excel forum,, "tbo" wrote: > Programming question? Hopefully so. > Excel novice here > > I would like to color a row yellow at a certai...

Cell formatting 04-06-10
I am sure I am doing something incredibly stupid here but just cant seem to figure this out. I have a budget work sheet. The first column is the account number. the next twelve columns are each month's budget (Jan-Dec). In the original budget file, some numbers were simply keyed in and others are formulas. (Some are a gross # times a percentage and others a gross # divided by 12 months). I am creating a text file to import the budget data into my financial system. The first thing I did was to take the budget file and copy it into its own file by using paste spe...

How to get the Mainwindow of Excel via COM ?
Hello there, I didnot found a methode in the Exel VBA-Documentation to get the MainWindow of Excel. Now Im using CWnd::FindWindow("XLMAIN",NILL). But is not there a COM-Function ? Thanks Mark I dont know if there is a function but that will be available thru the Object Model of Excel if its exposed by it. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Mark Henning" <mhenning@gmx.net> wrote in message news:unG\$y6NNEHA.2336@TK2MSFTNGP09.phx.gbl... > Hello there, > > I didnot found a methode in the Exel VBA-Documentation to get the MainWindow > of E...

Paste into visible cells
I have a situation where I'm trying to copy a range (e.g. A1:D5) into a sheet with hidden rows. I cannot find a way to paste into visible cells only. (I can copy FROM visible cells only, but cannot paste TO visible cells only). Does anyone know if there is a way to do this? -- ERR229 Sometimes you can sort the range first--so that your intended paste range is nicely grouped. Then paste. ERR229 wrote: > > I have a situation where I'm trying to copy a range (e.g. A1:D5) into a > sheet with hidden rows. I cannot find a way to paste into visible cells only. > (I can c...

How to protect a cell INSTANTLY the moment the data is entered!!
Hello, I want to know how to protect the cell at the very moment data i keyed in and entered without using the protection option in the tool' bar! I would like the process to be automatic!! for example: in cell B if i type in the word "king" and press ENTER! after this i must not b able to modify the word as the cell should be automatically protected if any one has a response to this please reply i would be eagerl waiting for it! thanx! Mot -- Message posted from http://www.ExcelForum.com You could do this by having the cell unprotected>entering the data>have a workshee...

Project 2007
One of my students has some tasks which will be repeated at different points during the project (Reoccurring task won't work in this case). Is there a way to copy and paste his extensive note information without having to open the Task Information window each time? Selecting the task row to copy and paste won't bring over the notes. In article <8FB23A12-C779-4370-9749-D9758403B352@microsoft.com>, Mikel Anne <MikelAnne@discussions.microsoft.com> wrote: > One of my students has some tasks which will be repeated at different points > during the project (...

Avoiding circular reference on formula
Hi, There is a formula called retail ratio which is Product Sales divided by the sum of Product Sales and Service Sales. or RR = P/(S+P). I am trying to create a "What If" chart which would allow someone to plug in values for the Retail Ratio. The Service Sales are also known. What I am trying to do is to solve for what the Product Sales would be. So RR and S are know but P is not. Since it is both in the numerator and denomiator, I am trying to come up with a formula that solves for this. Here is what my chart looks like. Can someone help? Thanks, Chuck Person ...

table not getting updated
I have a form with info derived from a query. It opens from another form with default data in some of the fields. While trying to save information programmably, i can not get this to happen. The only time that I can get the info to update in a table is when I actually change one of the default values. Can anyone help we determine a method to use to force the action of data moving to the table? thanks kkruz wrote: > I have a form with info derived from a query. It opens from another > form with default data in some of the fields. > While trying to save information programmabl...

Need help (Average excluding zero values)
This is what i have & it works but one problem there will be zero values & i need to exclude them if possible =AVERAGE(IF(AA5="L",E\$5:E\$65)) I have tried several different combinations I can,t get it that includes zero values in the average for me. are they blanks and not zeroes? -- Gary Keramidas Excel 2003 "Mike" <Mike@discussions.microsoft.com> wrote in message news:29096CBB-3E2A-4828-8A49-6CEB0142D5C4@microsoft.com... > This is what i have & it works but one problem there will be zero values & i > need to exclude them...

formula to subtract time, 12:10 am (00:10)
Hi One way With 00:10 in A1 and 23:50 in B1 =MOD(A1-B1,1) -- Regards Roger Govier "teezee" <teezee@discussions.microsoft.com> wrote in message news:BAAF0132-424F-4491-B9E8-0ABFB9B4D0CB@microsoft.com... > ...

In Excel, how do I get rid of duplicate records? Based on text r.
I work with long lists of people's names and addresses for marketing. I would like to find a shortcut to get rid of duplicate names and addresses instead of manually looking at each record. I looked at Functions and found Exact but that just identifies duplicate records and I find the functions to be complicated if not shown how to create the proper syntax. Thanks. You might like to have a look at Chip Pearson's web page on working with duplicates - http://www.cpearson.com/excel/duplicat.htm Hope this helps cheers JulieD "White T-Bird Kitten" <White T-Bird Kitte...

display red value if true?
Let's say that I make a logical test...the test is true and it displays a certain value... Example: =IF(A1>0;"blablabla") (the logical test is "A1>0" and the value if true is "blablabla" My problem is that I need the "blablabla" to appear [Red], but only if the logical test is true. If it's false the color should be "Automatic" or whatever I've selected. Hope someone understands my question, cus I really need a solution... I've tried this, but of course it didn't work: =IF(A1>0;"blablabla";IF(A1>0;[...

Do a two way lookup and get the result in multiple columns
Hi, I have a worksheet with data on store sales: StoreLoc Date Sales\$ A 10/28/2005 \$100 A 11/04/2005 \$150 B 10/28/2005 \$200 B 11/04/2005 \$250 B 11/11/2005 \$300 B 11/18/2005 \$350 C 11/04/2005 \$300 C 11/11/2005 \$350 C 11/18/2005 \$400 D 11/11/2005 \$400 etc. I wanted to do a two-way lookup on StoreLoc and Date and get the sal...

Array Formula isn't summing but counting
I need to sum information based on several other criteria -- accoun number, sort codes, company names. The array formul {=SUM(A1:F25="A")*(E1:E25>0))} returns a count rather than a sum Does anyone know of a way get it to add my data rather than countin how many? Staci -- Message posted from http://www.ExcelForum.com Hi if you want to sum column E try the array formula =SUM((A1:F25="A")*(E1:E25>0)*(E1:E25)) or a non array formula =SUMPRODUCT((A1:F25="A")*(E1:E25>0),E1:E25) -- Regards Frank Kabel Frankfurt, Germany > I need to sum information ...