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. Follow

1 Replies
482 Views

Similar Articles

[PageSpeed] 42

=indirect(yourformulahere)

Don't include the equal sign in your formula.  And match the name correctly (Jul
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 (12005)
7/26/2009 3:29:39 PM
Reply:

Similar Artilces:

Excel crashes when entering numbers and formulas into cells
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel This only happens with one file...I have tried switching users, bringing it over to Sun Office and then bringing it back, bringing it to 2004 and back, reinstalling 2008, etc. It works fine with Windows 2003. <br><br>I have also copied and moved tabs to a new spreadsheet and the problem still occurs after a few hours of productivity. <br><br>I think the file is corrupted as other spreadsheets work fine. Any thoughts on how to fix? <br><br>Thanks much! Andy <br><br&...

dynamic formulas
Hi there. I have excel documents named like January.xlsx, Feb...xlsx then to December.xlsx. And I have a huge file named Year.xlsx which has links from these month files (all formulas are the same, sama cell numbers, same worksheet names etc..). What I would like to know is is there any way to choose the month name and excel could update itself. for example: ='[January.xlsx]Workseet 1'!$G15 Lets say I will resever A1 for data cell and will make a dropdown list there. When I select February I'd like excel to update all formulas from Jan to Feb. It sounds too h...

"IF" Formula Help #2
Thanks Jack, Worked brilliantly Zac -- Korgan2 ----------------------------------------------------------------------- Korgan26's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1447 View this thread: http://www.excelforum.com/showthread.php?threadid=26138 ...

Replace Cell Address in A Long Formula
From time to time, I need to do some change in some very long formula. For example: =TRIM(IF(ISERROR(FIND(" ",B46,FIND(" ",B46,1)+2)),MID(B46,FIND(" ",B46,1)+1,LEN(B46)),MID(B46,FIND(" ",B46,FIND(" ",B46)+2),LEN(B46)))) I want to replace all the "B46" with "F18". Is there a easier way? Thanks. One quick and dirty way is to Edit>Replace B46 with F18. HTH Anders Silven "plumstone" <plumstone@discussions.microsoft.com> skrev i meddelandet news:29A2448B-1055-4847-9779-EA0FDDA5C05D@microsoft.com... > From...

formula displaying only right-hand characters
Hello: Although I am going to be using this as a Crystal formula, this would likely work in SQL Reporting Services or Excel as well. In GP 10.0, there are a string of characters of course representing Inventory Item IDs. Some of these IDs have a dash in the middle followed by several alphanumeric characters, afterward. The number of characters after the dash is anywhere from 3 to 7 and can include a period. The number of characters to the left of the dash is 5. So, the dash is in the 6th place, while the characters after the dash begin at the 7th place. I want to use that formula t...

is there a way to get number of "objects" in a Access db?
I have a mdb with a large number of forms open at the same time. I think I might be hitting the 32768 object limit. Is there any way to monitor the object count so I can prove or disprove my suspicions. DaBears - This will cound all the objects in the MSysObjects table. Obviously, don't mess with the system tables... SELECT Count(MSysObjects.Id) AS CountOfId FROM MSysObjects; -- Daryl S "DaBears" wrote: > I have a mdb with a large number of forms open at the same time. I think I > might be hitting the 32768 object limit. Is there any way ...

How to assign a value as a Named Area
I would like to write a VBA to make some copies automatically. The range is to be selected based on a cell's value. For example : if A1 = "General Admin" Then print area named "GA" if A1 = "Sales Admin" Then print area named "SA" My VBA is : Dim x As String If ActiveCell.Value = "General Management" Then x = "GA" ElseIf ActiveCell.Value = "Sales Admin." Then x = "SA" ElseIf ActiveCell.Value = "Beijing" Then x = "BJ" ElseIf ActiveCel...

text in cell looks different when printed
text in cell looks ok on screen. when printed, text is moved around, out of alignment from the way the cell looked when viewing the screen. When keying in information into a cell, how do you get the text to skip a line? when I press enter key, the pointer moves to the next cell. Hi I would check your printer driver for your first problem. How does it look on Print Preview? For your second question, use Alt Enter and this will insert a carriage return into the cell. -- Andy. "thomas brown" <anonymous@discussions.microsoft.com> wrote in message news:35c101c42944$...

How do I get the same contacts to show up on all of my computers.
In our office we have five computers that all have the same program, however when a new contact is entered some contacts you can access it from all the computers and some you can't, how can I make so that all of the information shows up on all of the computers? The program on all 5 computers is.................?? Gord Dibben MS Excel MVP On Mon, 25 Sep 2006 08:23:02 -0700, JLebron <JLebron@discussions.microsoft.com> wrote: >In our office we have five computers that all have the same program, however >when a new contact is entered some contacts you can access it from a...

Formula to lookup named reference based on value
I cant' figure out what the exact words that I'm looking for and if I did I would most likely find the answer. I've got a spreadsheet that has a series of numbers in say column A and it has the numbers 1,2,3,4,5 Further on in the spreadsheet I have named values like CA.1, CA.2, CA. 3 What I want to do is in the formula go: =(X1/SUM(X:X,X,X:X)*CA.2) (for the named range CA.2) Is it possible to have something that goes.... =(X1/SUM(X:X,X,X:X)*"CA"&B4) where B4 = 2 Thus the outcome would be CA.2 ????? =(X1/SUM(X:X,X,X:X)*INDIRECT("CA"&B4)) -- Kind...

How do I get the REPLY button to show up on top of each email?
I lost my reply button at the top of each email. How do I get it back? Thanks for your help! By asking in a group relevant to your email client? "mjmapes" <mjmapes@discussions.microsoft.com> wrote in message news:420D315B-5ED0-4A95-8FD2-57211B2CBB69@microsoft.com... :I lost my reply button at the top of each email. How do I get it back? : Thanks for your help! ...

How do I expand the number of characters allowed in an Excel cell.
The default for maximum number of characters in a single Excel cell is 1024. If you enter more than that all you see is astricks (********). If you select the cell where the astericks show, you can read the information in the formula bar above. However, it does not print out well. I am importing web data from a Web Based Application into Excel and require a column for comments which normally falls below the default maximum. On those occassions it does not, it is a pain. I have run into this problem for quite a while and have finally decided to put it out there for the more advan...

Run a Hyperlink from another cell
I have a master spreadsheet and a cutdown spreadsheet. I have a hyperlink to a file in the master spreadsheet which I want to link to on the cutdown spreadsheet. At the moment if I click on the hyperlink on the cutdown spreadsheet it opens the master and goes to the hyperlink cell reference. I want to open the file from the original hyperlink when I click on the cell in the cutdown spreadsheet. I hope somebody can help. ...

Min and Max Value Changes
I want to just label and highlight the min and max values in an XY chart and have them move appropriately if the data change. i saw a tutorial on line about doing so in 2003 but I can't get it to work in 2007. -- Dave B Tell us how you did it in 2003, the hopefully we'll be able to tell you how to do it in 2007. I would have expected that on your data sheet you would have formulae to give the min and max, and then you'd use those as new series to add to your chart. -- David Biddulph "Dave B" <DaveB@discussions.microsoft.com> wrote in message ...

Formula for weeks
I was wondering if there is a formula to calculate how many weeks it is from today to a certain date This formula will calculate how many days =DATEDIF(Date1,Date2,"d") or =DATEDIF(A1, B1,"d") with dates in A1 and B1 If by week you mean any seven days (not a Sun to Sat) then =DATEDIF(A1, B1,"d") /7 best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Shihachi" <Shihachi@discussions.microsoft.com> wrote in message news:EEBBAFED-0786-40C5-9E1F-57901EFD456F@microsoft.com... > I was wondering i...

Insert a space in all blank cells of the first column
Dear Experts: For some specific reasons I would like to enter a space in all blank cells of the first column of my spreadsheet called sheet 1. How is this done by using VBA? Help is much appreciated. Thank you very much in advance. Regards, Andreas First, this is usually a mistake to do. It'll mess up formulas like: =if(a1="","it looks empty","it doesn't look empty") If you have other formulas that use these empty cells--like: ='sheet 99'!a1 and you're seeing 0's where you don't want them, try modifying your formul...

formula needed #4
Thanks for the help. I like that way a lot better. I actually got a formula to work, and it probably follows along with the suggested earlier. It is written with the names of the tabs I had to use, but maybe someone else will be able to use it. Again, thanks to everyone. =SUMPRODUCT(--('Call Frequency'!$A$1:$A$50000<>"")/COUNTIF('Call Frequency'!$A$1:$A$50000,'Call Frequency'!$A$1:$A$50000&""),--('Call Frequency'!$C$1:$C$50000='Repeat Model Type Breakdown'!A2)) Gary's Student Wrote: > You can accomplish your coun...

Formula vs Constant #2
Is there a formula that will check a cell to determine if it is a constant (123) vs a formula (=a1)? Only if you use a UDF http://www.mvps.org/dmcritchie/excel/formula.htm#HasFormula -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Jonathan Cooper" <JonathanCooper@discussions.microsoft.com> wrote in message news:2ED0251B-E26A-46C3-803C-9B8FA51EEE5D@microsoft.com... > Is there a formula that will check a cell to determine if it is a constant (123) vs a formula (=a1)? Jonathan User Defined ...

Conditional formating an entire row vs. only one cell
I have 400 rows of data. There are four columns that contain any one of five different key letters (R, NR, O, U, NA). What I want to do is that everytime a letter such as NR shows up in any of the four columns, the entire row has a conditional format applied. When I do "Cell Is", the conditional format is only applied to the one cell that meets the condition even if I have the entire row(s) highlighted when I create the conditional format. I tried to find similar posts to help but have not been successful. Using Excel 2003. Highlight all your data (assuming you have start...

how to skip the blank cells
hi, i copied some cells including blank, but after checking the skip blank button when i try to paste the blank cells are also copied. Select the copied cells including the blank... ....Select Edit then Go to (ctrl G) Click the special botton...select BLANKS click OK the performed delete command >-----Original Message----- >hi, >i copied some cells including blank, but after checking the skip blank >button when i try to paste the blank cells are also copied. >. > ...

Hide rows if cells = 0.00
I am trying to hide rows if any cell in a columm = 0.00. Example: If any cell in column D = 0.00 than hide the row containing that cell. Highlight your data, then from the menu bar select Data > Filter > Autofilter. Select the drop-down in column D and choose Custom. Use the drop-downs to set your selection to read 'does not equal' '0.00', then click OK. Those rows with 0.00 in column D should now be filtered out (they're still in your data, just not visible). --Bruce "dford" wrote: > I am trying to hide rows if any cell in a columm = 0.00. Exa...

Zero decimal getting dropped.
I produce a report with a number to 3 decimal places. If the last decimal place is a zero it drops it and only prints two decimal places. This makes the whole column look jagged. I put in Properties, Decimal Places 3, but it still drops it. What to do? Thanks. Use the Format() function to turn it into a string value with three decimals. Leading and trailing zeros in a numeric field make no sense and as such are dropped. Phil wrote: >I produce a report with a number to 3 decimal places. If the last decimal >place is a zero it drops it and only prints two decimal places. This ...

Counting cells basesd on multiple criteria
Ok so i have two sheets In SHEET 1 column A i have a list of about 1000 different alarms. Then In SHEET 2 i have a log for 30 days with the alarms that occured in those thirty days. The log takes up 35465 thousand rows. Column D has the name of the alarm and Column C has if the alarm was an IN or OUT alarm. What i want to do is in SHEET 1 column 2 display how many times each of these 1000 alarms occured in the last 30 days but only when it was an IN alarm. I cant use something that refrences the name of the alarm i need a formula that can actually refrence the cell because it would be ...

get the latest day of the previous month
hello, i have a date variable, and i would like with VBA to get the latest day from the previous month many thanks Function LastPrevDate(Dt As Date) As Date LastPrevDate = DateSerial(Year(Dt), Month(Dt), 0) End Function Sub test() MsgBox LastPrevDate(Now) End Sub HTH. Best wishes Harald "Laurent M" <anonymous@discussions.microsoft.com> skrev i melding news:06a701c503b0$3a4b7bc0$a401280a@phx.gbl... > hello, > > i have a date variable, and i would like with VBA to get > the latest day from the previous month > > many thanks In case you specified VBA be...

How can I get background fill in Publisher to cover a whole page
I am using Publisher 2002 with windows XP. When editing I have a frame which covers trhe whole page and has a background fill. When printing there is a white border all around, however big I make the frame. I am printing on A4 paper. How can I get this fil;l to cover right to the edges? Does your printer support full bleed? -- JoAnn Paules MVP Microsoft [Publisher] "acmr" <acmr@discussions.microsoft.com> wrote in message news:ED29C702-96FD-44FF-ABAE-0196169147FD@microsoft.com... >I am using Publisher 2002 with windows XP. When editing I have a frame >which >...