The formula im using works like a charm until i change the text reference for "43" to a cell reference which updates using the =weeknum formula. What do i need to add here?
=INDEX('Online sales'!$B$2:$F$9,MATCH("Alabama",'Online sales'!$A$2:$A$9,0),MATCH("43",'Online sales'!$B$1:$F$1,0))
Thanks in advance!
so this doesn't work (with 43 being a number)?
=INDEX('Online sales'!$B$2:$F$9,MATCH("Alabama",'Online sales'!$A$2:$A$9,0),MATCH(43,'Online sales'!$B$1:$F$1,0))
otherwise,
if you want to keep the weeknum formula as a text output, you could for instance wrap it in TRIM() or ""&WEEKNUM(), etc; somehow convert the number to text...
If that formula works that means the data in Online sales!$B$1:$F$1 are TEXT numbers.
The best thing to do is change those values to numeric numbers.
Are they results of formulas?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Right both of these points make sense but what im ultimately trying to do is make the 43 a dynamically changing number based on a cell reference that will change each week. I do see how im referencing text and i should format as numbers in the instance above but that is not my goal. Thanks for the reply.
Then you have to convert the value of the cell reference to be a text number.
Something like this:
=INDEX('Online sales'!$B$2:$F$9,MATCH("Alabama",'Online sales'!$A$2:$A$9,0),MATCH(A1&"",'Online sales'!$B$1:$F$1,0))
Not sure what you did but this seems to have worked. thanks a bunch!Originally Posted by Tony Valko
We concatenated an empty text string to the cell reference. If the cell reference contained a numeric number this concatenation changes that to a TEXT value.
For example:
10 = numeric number
10&"" = 10 as a TEXT value
In Excel TEXT numbers are not the same as numeric numbers.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks